Page 1 of 1

ORDER BY / GROUP BY on two columns with postgreSQL

Posted: Wed Jul 16, 2003 10:37 pm
by Slippy
Does anybody know the proper SQL to make a select statement that is sorted on two colums. That is columnA first and columnB second? :oops:

Posted: Thu Jul 17, 2003 3:37 am
by twigletmac
Not a postgreSQL user (but there are a few of them about so I'm sure they'll correct this if it's way off the mark) but maybe it's similar to things like MySQL where to sort on two columns you would do something like:

Code: Select all

SELECT field1, field2, field3, field4 FROM table ORDER BY field1, field2
Mac

Posted: Thu Jul 17, 2003 8:19 am
by nielsene
Yup, that's correct. List the columns to sort in "major" order -- most significant first, comma seperated. Ditto for group by.

Posted: Thu Jul 17, 2003 8:30 am
by Stoker
that syntax is the same for most (all?) rdbms with an SQL api, you can also sort with different directions (ASC/DESC) on those and based on columns in different tables when using joined queries

ORDER BY tableX.columnA DESC, table7.column5 ASC, php.geek DESC

Posted: Thu Jul 17, 2003 8:33 am
by twigletmac
That's what I thought but didn't want to assume in case postgreSQL did things differently - most DB vendors do tend to add their own imbellishments to the SQL standard.

Mac

Posted: Thu Jul 17, 2003 8:53 am
by Stoker
yeah and mysql is one of those that do several things very different than many others.. Main differences when it comes to the ORDER BY clause itself is if the RDBMS supports sorting on aliases or not, so it is always better to assume not..

e.g. this may work on some
SELECT (x+y) AS n FROM beer ORDER BY n ASC
but it will work on most (all?) with
SELECT (x+y) AS n FROM beer ORDER BY (x+y) ASC

Posted: Thu Jul 17, 2003 3:00 pm
by Slippy
Ah perfect... it works!

I was just too lazy to RTFM and performed the task with only one sorted column... I then went back and changed it to 2 columns...

Thanks a bunch!

On a side note -- is it true that most RDMS comply to SQL92 - or I am I way off here?

Posted: Thu Jul 17, 2003 3:06 pm
by Stoker
Most RDBMS has quite a bit of the SQL92 functionality, but most of them also has their own extensions and may lack a few things, and, several things that are not defined in SQL92 they have made their own flavor of..

I think that PostgreSQL is probably the one that strive the most to be SQL92, and in the future, SQL99 compliant and incorporate more and more of the functionality described in the standards..