Php sql ORDER BY --

Question

Users Table:

ID|Name|Deleted
1 |    |  N 
2 | b  |  Y 
3 | c  |  N
4 | d  |  N

Deleted default value is 'N'

I want to order by id desc but I want to display deleted user last.

i.e. when Deleted 'Y' should appear last

Gordon Linoff · Accepted Answer · 2016-03-22 03:16:52Z

5

You can use multiple keys in order by. So:

order by (deleted = 'Y') asc, id desc

The expression (deleted = 'Y') is a boolean expression that MySQL treats as a number with 1 for true and 0 for false. The asc puts the true values last.

In most other databases, you would do this with a case:

order by (case when deleted = 'Y' then 1 else 2 end) desc, id desc

answered Mar 22, 2016 at 3:16

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

4 Comments

Sean Over a year ago

would it be possible to also just do ORDER BY deleted, id DESC (or ORDER BY deleted ASC, id DESC) since the values N/Y are alphabetic order?

Gordon Linoff Over a year ago

@Sean . . . Yes, that is also possible. I tend to be cautious, just in case there could be other values.

Mohammed Kumel Over a year ago

THANKS a lot people.

jarlh Over a year ago

The (deleted = 'Y') version also works well any ANSI SQL compliant dbms that supports boolean. If deleted = 'Y', then TRUE is returned, else FALSE. And FALSE is ordered before TRUE.

Thejas · Accepted Answer · 2016-03-22 03:41:25Z

0

This will be the simple solution

select * from table_name ORDER BY deleted ASC, id DESC

answered Mar 22, 2016 at 3:41

Thejas

3536 silver badges19 bronze badges

Collectives™ on Stack Overflow

Php sql ORDER BY --

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related