Postgres not sorting two columns correctly

Question

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?

podiluska · Accepted Answer · 2012-09-13 12:18:27Z

3

user is a reserved word - returning the current user name

Try

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

answered Sep 13, 2012 at 12:18

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

6 Comments

mouckatron Over a year ago

That was it. Thanks! I changed it to AS name instead and sorted on that.

Michael Buen Over a year ago

@mouckatron You don't need the concatenation between firstname and surname when doing ORDER BY. This would suffice: ORDER BY u.firstname,u.surname. I cringe when I see concatenation on ORDER BY, it's useless, and has side-effects, it's for you to find out

podiluska Over a year ago

@MichaelBuen You can't possibly say that without knowing the values of the data. If there are two records ('a b', 'c') and ('a', 'b c') your proposal returns a different result. In order to preserve the intent of the original query, the concatenation is necessary.

podiluska Over a year ago

So you've given a SQL fiddle where the resultset proves me right, and still you can't see the difference?

Michael Buen Over a year ago

@podiluska RE: "proves you right"? You can't even see that ordering by concatenation would return wrong result? Zuckerberg should come last. Your query return Zuckerberg as second. There's nothing sqlfiddle(concatenation approach vs ordering by fields directly) that proves you right, in fact it indicates otherwise. Your query is really not correct

|

Steve · Accepted Answer · 2012-09-13 12:22:54Z

0

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.

answered Sep 13, 2012 at 12:22

Steve

2451 silver badge6 bronze badges

Comments

Robert · Accepted Answer · 2012-09-13 12:29:22Z

0

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;

edited Sep 13, 2012 at 12:29

answered Sep 13, 2012 at 12:19

Robert

25.8k8 gold badges70 silver badges86 bronze badges

1 Comment

mouckatron Over a year ago

Good answer which would also work now that I've read the documentation on order by more thoroughly. Thanks

Collectives™ on Stack Overflow

Postgres not sorting two columns correctly

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related