CODEWITHSUNDEEP
sqloracle-databaseplsql
ThePallav_Abhi
ThePallav_Abhi

Reputation: 119

Order by with a particular value on TOP

I have a requirement to order a list of countries alphabetically but with a specific country on TOP. After that country it should be ordered alphabetically . Example

India
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
Antigua and Barbuda
Argentina
Armenia
Aruba

........... I tried the answer provided here Sorting certain values to the top but it was not working I am using PL/SQl dev tool. Thanx in Advance

Upvotes: 4

Views: 16858

Answers (6)

D-Lock
D-Lock

Reputation: 11

  1. If you can add a column to your data, add something like CountryOrder, put 0 for India and 1 for all other countries, then ORDER BY CountryOrder
  2. If you cannot or have no wish to add another column, you can make this virtually with the CASE in the ORDER BY:

The same way you can add additional countries:

ORDER BY CASE WHEN Country = 'India' THEN 0
              WHEN Country = 'China' THEN 1
              ELSE 2
              END, 
              Country;

You sort by 0,1,2 first, and then by Country (ascending by SQL default).
This way you will get the result:
India
China
Afganistan
Albania
Algeria
Argentina
Armenia
Aruba
etc.

Upvotes: 1

Murshid Mansuri
Murshid Mansuri

Reputation: 21

you can try this.

SELECT * 
FROM `country` 
ORDER BY CASE WHEN countyName = 'india' THEN 1 ELSE 2 END, countyName ASC

Upvotes: 2

Stranger
Stranger

Reputation: 10610

There is an another simple way to get the desired result by using FIELD function.

Generally FIELD function will return TRUE if the value matches.

For eg., if a table structure is like this,

enter image description here

ORDER BY FIELD(first_name, 'PENELOPE') will return the position of the value in order by FIELD. Since PENELOPE is in the first position it will return 1, when the value is not present inside the FIELD, it will return 0,

ORDER BY FIELD(first_name, 'PENELOPE') DESC

In case if you want to sort multiple items at the top, then you can use,

ORDER BY FIELD(first_name, 'PENELOPE', 'BETTE') DESC

In the above example, the first_name with PENELOPE will come first, then first_name with BETTE, then the other values.

Upvotes: 2

ThePallav_Abhi
ThePallav_Abhi

Reputation: 119

Thanx all for the response. This way i tried.

 SELECT *
        FROM (SELECT L.LOOK_UP_CODE, TO_CHAR(L.CITY) LOOK_UP_DESC
                FROM GHCM_IN_CITIES_MAPPING_DTLS L, GHCM_LOOK_UP_TBL A
               WHERE L.ENABLED_FLAG = 'Y'
                 AND L.STATE = IN_STATE
                 AND A.LOOK_UP_TYPE = 'LOCATION_VALUE'
              UNION
              SELECT A.LOOK_UP_CODE LOOK_UP_CODE,
                     A.LOOK_UP_DESC LOOK_UP_DESC
                FROM GHCM_LOOK_UP_TBL A
               WHERE A.LOOK_UP_TYPE = 'LOCATION_VALUE')
       ORDER BY (CASE
                  WHEN LOOK_UP_DESC = 'Others' THEN
                   'ZZZ'
                  ELSE
                   LOOK_UP_DESC
                END);

And its working perfectly.

Upvotes: 1

TheCarver
TheCarver

Reputation: 19713

Something like this should work:

MySQL VERSION

ORDER BY (country = 'India') DESC, country ASC

SQLFIDDLE DEMO

-- or --

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1
       ELSE 2
    END,
    country ASC

SQLFIDDLE DEMO

ORACLE VERSION

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1 
       ELSE 2
    END

or you can have more than one specific value at top:

ORDER BY 
    CASE 
       WHEN country = 'India' THEN 1
       WHEN country = 'United Kingdom' THEN 2
       ELSE 3
    END

Upvotes: 17

Yaroslav Shabalin
Yaroslav Shabalin

Reputation: 1644

If there is no NULL values in the country column then you can use that one:

ORDER BY NULLIF(country, 'India') ASC NULLS FIRST

Upvotes: 2

Related Questions