CODEWITHSUNDEEP
sqlsql-servert-sqlsql-order-by
mimi
mimi

Reputation: 170

Order by condition in T-SQL

I've got two result columns. One is username and the other one is date-time which can check when each user viewed a post. If user hasn't seen the post, date-time value is NULL.

I'd like to order the data like this. date-time is null or not null both are order by username alphabetically. but null data and not null data should be separated. 

SELECT u.username AS username,
       MAX(w1.date) AS lastdate
FROM w1
     RIGHT OUTER JOIN user_info u ON w1.userid = u.userid
WHERE u.usergroup = 'student'
GROUP BY username
ORDER BY lastdate,
         username;

When I run this query, the result I can get is username is ordered by alphabetically when date-time is NULL, but when date-time is not null, it's ordered by date-time.

I hope you guys can understand, if it's not clear enough please leave a comment.

Upvotes: 1

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I would recommend using left join and phrasing the query as:

SELECT u.username AS username,
       MAX(w1.date) AS lastdate
FROM user_info u LEFT JOIN
     w1
     ON w1.userid = u.userid
WHERE u.usergroup = 'student'
GROUP BY u.username
ORDER BY (CASE WHEN w1.date IS NULL THEN 1 ELSE 0 END),
         u.username;

SELECT DISTINCT is almost never appropriate with GROUP BY.

I also recommend qualifying all column references in a query that references multiple tables.

Upvotes: 4

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

You can use

order by case when lastdate is null then 0 
              else 1 end, username

Upvotes: 5

Related Questions