Mysql use index keyword as column name

Question

Hi in my project I have a requirement of using the index keyword in a mysql query in order by clause.

My query looks like this:

     SELECT asset.id, day_part.category, asset.site_id, site.name,
            environment.category_1, environment.category_2, county.town, county.county,
            site.site_id as siteid, media_owner.contractor_name,
            IF (audience_category.impact IS NULL, 0, audience_category.impact) as impact,
            tv_region.id as tv_region_id,
            metropolitan.id as metropolitan_id,
            IF (
                price.price_site = -1,
                IF(
                    price.price_tv_region = -1,
                    price.price_nation,
                    price.price_tv_region
                ),
                price.price_site
            ) AS price,
            format.name AS format,
            format.id AS format_id
        FROM asset
        JOIN site ON asset.site_id = site.id
        JOIN day_part ON asset.day_part_id = day_part.id
        JOIN media_owner ON site.media_owner_id = media_owner.id
        JOIN area ON site.area_id = area.id
        JOIN environment ON site.environment_id = environment.id
        JOIN price ON site.price_id = price.id
        JOIN postcode ON site.postcode_id = postcode.id
        JOIN county ON postcode.county_id = county.id
        JOIN tv_region ON postcode.tv_region_id = tv_region.id
        JOIN metropolitan ON postcode.metropolitan_id = metropolitan.id
        LEFT JOIN temp_media_index_1395751552 as audience_category
            ON audience_category.site_id = site.id
        JOIN frame_grouped ON frame_grouped.site_id = site.id
        JOIN format ON frame_grouped.format_id = format.id
        WHERE frame_grouped.format_id = 3

        ORDER BY index DESC
        LIMIT 100 OFFSET 0

This query is giving this error (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index DESC\n LIMIT 100 OFFSET 0' at line 35")

But when I remove order by clause from query, it's not giving any error.

Can anyone please suggest how to use the index keyword of mysql in my query?

I think, where did you get the index like tablename.index or mention the alies name like somedetails AS index — jmail
– jmail, Commented Mar 25, 2014 at 14:25

ToolmakerSteve · Accepted Answer · 2015-07-22 18:44:07Z

16

Index is a reserved word. Quote it (with back-ticks):

ORDER BY `index` DESC

edited Jul 22, 2015 at 18:44

ToolmakerSteve

22.1k16 gold badges114 silver badges233 bronze badges

answered Mar 25, 2014 at 14:07

alexn

59.2k14 gold badges114 silver badges146 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

alexn Over a year ago

What error do you get when you quote it? It looks good to me. Make sure you don't have any quotes or other characters before or after your query.

Kickstart Over a year ago

@question - are you surrounding it with quotes or back ticks? Should be back ticks.

Collectives™ on Stack Overflow

Mysql use index keyword as column name

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related