skip to Main Content

i want my query to return a specific row of the table where a column contains specific value first and with year desc.

if i have table something like this

Table employee

 id  - Name    -    Year - Status 
  1  - Ashish  -    2016 - Old
  2  - Srisan  -    2017 - New
  3  - Mohit   -    2018 - New
  4  - Ram     -    2015 - Old
  5  - Boby    -    2016 - New

then result should be

 id -  Name   -  Year - Status 
  3 -  Mohit  -  2018 - New
  2 -  Srisan -  2017 - New
  5 -  Boby   -  2016 - New
  1 -  Ashish -  2016 - Old
  4 -  Ram    -  2015 - Old

Where Status is new and sort via desc of Year

3

Answers


  1. You can try below –

    select * from tablename
    order by case when 'New' then 0 else 1 end, year desc
    
    Login or Signup to reply.
  2. Sort by FIELD(Status) and Year

    SELECT id, Name, Year, Status 
    FROM myTable
    ORDER BY FIELD(Status, 'New', 'Old'), Year DESC
    
    Login or Signup to reply.
  3. For expected result as mentioned in your question, query would be

    SELECT * FROM employee  ORDER BY Status ASC, Year DESC;
    

    it has all rows but if you want specific rows then you have to use Where clause as mentioned below as per your required condition.

    SELECT Name FROM employee where Year=2018 ORDER BY Year DESC;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search