CODEWITHSUNDEEP
sqlpostgresql
mouckatron
mouckatron

Reputation: 1289

Postgres not sorting two columns correctly

I am selecting a name, an action and a count from Postgres sorting by name ASC, action ASC like so:

SELECT u.firstname || ' ' || u.surname AS user,
    nt.name AS action,
    cn.count
FROM (
    SELECT actioned_by_id, note_type_id, COUNT(id) AS count
    FROM customer_notes
    WHERE actioned_date IS NOT NULL 
    GROUP BY actioned_by_id, note_type_id
) AS cn
LEFT JOIN note_type AS nt ON cn.note_type_id = nt.id
LEFT JOIN users as u on cn.actioned_by_id = u.id
WHERE cn.actioned_by_id IS NOT NULL 
ORDER BY user, action;

however the results show that it is ignoring the user clause and only ordering by the action. 

"ADMIN USER" "CALL OUT"   1
"ADMIN USER" "EMAIL"      1
"ADMIN USER" "LETTER"     2
"AA AA"      "MEETING"    1
"ADMIN USER" "PHONECALL"  7
"AA AA"      "PHONECALL"  1

Anyone understand why? And how to make it order properly?

Upvotes: 0

Views: 2987

Answers (3)

Robert
Robert

Reputation: 25763

Try to order using columns' positions

SELECT u.firstname || ' ' || u.surname AS user,
    nt.name AS action,
    cn.count
FROM (
    SELECT actioned_by_id, note_type_id, COUNT(id) AS count
    FROM customer_notes
    WHERE actioned_date IS NOT NULL 
    GROUP BY actioned_by_id, note_type_id
) AS cn
LEFT JOIN note_type AS nt ON cn.note_type_id = nt.id
LEFT JOIN users as u on cn.actioned_by_id = u.id
WHERE cn.actioned_by_id IS NOT NULL 
ORDER BY 1, 2;

Upvotes: 0

Steve
Steve

Reputation: 245

Try this order clause:-

ORDER BY u.firstname || ' ' || u.surname, action;

I think it is ignoring the order by column USER, as that is a derived column - you need to specify the derivation itself, not the name of the derivation.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

user is a reserved word - returning the current user name

Try 

order by u.firstname || ' ' || u.surname, action

Upvotes: 3

Related Questions