CODEWITHSUNDEEP
mysqlsql
Sovat
Sovat

Reputation: 61

Can anyone help me to write sql statement?

Can anyone help me to write sql below?

Suppose:

tbl_request

No       Title       Date           Priority
1        AAA         2013-08-06     3
2        BBB         2013-08-04     1
3        CCC         2013-08-05     0
4        DDD         2013-08-02     4
5        EEE         2013-08-01     2
6        FFF         2013-08-04     0
7        GGG         2013-08-03     5
8        HHH         2013-08-03     0

There are two top priorities to order in sql statement:

I want to show all request that order with the 1st priority first (0 won't display). After 1st priority, i want to display the 2nd priority.

Here is what i want:

No       Title       Date           Priority
2        BBB         2013-08-04     1
5        EEE         2013-08-01     2
1        AAA         2013-08-06     3
4        DDD         2013-08-02     4
7        GGG         2013-08-03     5
3        FFF         2013-08-05     0
6        GGG         2013-08-04     0
8        HHH         2013-08-03     0

I don't know how to write sql statement to get the format above. Can anyone tell me how to write it?

Thank in advance.

Upvotes: 0

Views: 182

Answers (9)

Manish Sapkal
Manish Sapkal

Reputation: 6191

Select * from tbl_request 
Order by ( CASE WHEN Priority > 0 THEN Priority ELSE 99999999 END ), DATE DESC

Upvotes: 0

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

SELECT *
FROM Table1
ORDER BY field(`Priority`,0),`Priority`,
  `Date` DESC

FIDDLE

Upvotes: 0

Vikram Jain
Vikram Jain

Reputation: 5588

SELECT *
FROM Table1
ORDER BY 
  (CASE WHEN Priority = 0 THEN  9999 ELSE 1 END)  asc, date desc

Upvotes: 0

David Lin
David Lin

Reputation: 13353

You can try:

Select title, date, priority from tbl_request Order By IF(priority = 0, 99999, priority) asc, date desc

though it is ugly, inefficient, it works for you. Or you can consider making a union of to sub queries

Select title, date, priority from tbl_request where priority > 0 order by priority asc
union
Select title, date, priority from tbl_request where priority = 0 order by date desc

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166396

How about something like

SELECT *
FROM Table1
ORDER BY 
  CASE WHEN `Priority` != 0 THEN NULL ELSE 1 END,
  `Priority`,
  `Date` DESC

or

SELECT *
FROM Table1
ORDER BY 
  CASE WHEN `Priority` != 0 THEN 0 ELSE 1 END,
  `Priority`,
  `Date` DESC

SQL Fiddle DEMO

This will ensure that even if any of the priorities are greater than the other answers max values, this will still sort 0 as last.

Upvotes: 2

doitlikejustin
doitlikejustin

Reputation: 6353

If you aren't displaying 0 Priority records, then the below SQL statement will do just fine.

SELECT * FROM tbl_request WHERE Priority <> 0 ORDER BY Priority ASC, Date DESC

Upvotes: 0

Robert
Robert

Reputation: 25753

You should use case expression as below

select No,Title,Date,Priority
from tab
order by 
  case
     when Priority=0 then 9999
     else Priority
  end,
  Date desc

Upvotes: 0

Neil Hampton
Neil Hampton

Reputation: 1883

select no,title,date,priority
from tbl_request
order by if(priority=0,99999,priority),date desc

This assumes that no priority is over 99999. Yo also need to pit back quotes around no and date columns - I don't have them on this keyboard!

Upvotes: 0

Brian Hooper
Brian Hooper

Reputation: 22054

You could try something like:-

SELECT * FROM tbl_request
    WHERE Priority IN (1, 2, 3, 4, 5)
    ORDER BY Priority, DATE DESC
UNION
SELECT * FROM tbl_request
    WHERE Priority NOT IN (1, 2, 3, 4, 5)
    ORDER BY Date DESC;

Upvotes: 0

Related Questions