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?

Answer 1

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

ORDER BY `index` DESC