ORDER BY / GROUP BY on two columns with postgreSQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

ORDER BY / GROUP BY on two columns with postgreSQL

Post 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:
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post by nielsene »

Yup, that's correct. List the columns to sort in "major" order -- most significant first, comma seperated. Ditto for group by.
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Post 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?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post 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..
Post Reply