CODEWITHSUNDEEP
mysql
Felix
Felix

Reputation: 1263

sort result order by two fields using mysql

Now I am trying to get topics from my database.

topics (
  `replied_at` int(11) unsigned DEFAULT NULL,
  `created_at` int(11) unsigned DEFAULT NULL,
)

I want to sort topics by replied_at or created_at which is bigger.because I want to get the topic which is latest created or replied.

for example:

topic1:
replied_at: NULL
created_at: 1111

topic2:
replied_at: 2222
created_at: 0001

topic3:
replied_at: 3333
created_at: 1111

the result is :

topic3 topic2 topic1

Does mysql order by support this query?

thanks :)

edit:

I use this query but I got wrong order ):

SELECT * FROM topics ORDER BY GREATEST(replied_at, created_at) desc limit 3\G;

Upvotes: 0

Views: 58

Answers (2)

mvds
mvds

Reputation: 47034

select * from `topics`
         order by greatest(coalesce(`replied_at`,0),coalesce(`created_at`,0)) desc;

or, assuming that replied_at is always bigger than created_at:

select * from `topics`
         order by coalesce(`replied_at`,`created_at`,0) desc;

Upvotes: 2

juergen d
juergen d

Reputation: 204746

Use GREATEST()

order by greatest(ifnull(replied_at,0), ifnull(created_at,0)) desc

Upvotes: 1

Related Questions