CODEWITHSUNDEEP
mysqlforum
Ing. Michal Hudak
Ing. Michal Hudak

Reputation: 5612

SQL - order by 2 columns - order topics ( pinned first, then by created timestamp)

I have 1 table topics.

table

I want to order rows in this way.

first get rows where pinned = 1 order them by id ASC

second will be rows where pinned = 0 ordered by created_ts DESC

code:

SELECT topics 
WHERE category_id = :i AND deleted = :d 
ORDER BY pinned ASC, created_ts DESC

Note: I switched columns in table by mistake ( edited_by <=> edited_ts )

Upvotes: 0

Views: 380

Answers (2)

Peter Lang
Peter Lang

Reputation: 55584

You should be able to use CASE to only sort by a column if another column meets certain criterias:

SELECT id, title, ...
FROM topics 
WHERE category_id = :i AND deleted = :d 
ORDER BY pinned DESC,   -- first 1 then 0
         CASE WHEN pinned = 1 THEN id ELSE 0 END, -- ignore for pinned=0
         created_ts DESC

See example on SQL Fiddle.

Upvotes: 3

Wolph
Wolph

Reputation: 80061

How about something like this:

SELECT ...
ORDER BY pinned DESC,
    CASE WHEN pinned = 1
    THEN id
    ELSE -created_ts
DESC

Upvotes: 0

Related Questions