Thread: select statement sorting
is it possible to have postgres return a "SELECT * FROM table ORDER BY table_column" query that is not case sensitive order? if i have these words in a column: Alex alex Barbara Cohen i will get them back in the following order in the tuples: Alex Barbara Cohen alex But i want them back likke this: Alex alex Barbara Cohen Is this possible or do i need to sort them myself after the query returns? Also, i noticed that postgres will let me create groups, databases and users with spaces in their names, is this ok or should i check this beforehand and not allow this? thanks! -- Alexander Cohen http://www.toomuchspace.com (819) 348-9237 (819) 432-3443
Alexander Cohen wrote: > is it possible to have postgres return a "SELECT * FROM table ORDER BY > table_column" query that is not case sensitive order? > > if i have these words in a column: > > Alex > alex > Barbara > Cohen > > i will get them back in the following order in the tuples: > > Alex > Barbara > Cohen > alex > > But i want them back likke this: > > Alex > alex > Barbara > Cohen > > Is this possible or do i need to sort them myself after the query > returns? > > Also, i noticed that postgres will let me create groups, databases and > users with spaces in their names, is this ok or should i check this > beforehand and not allow this? > > thanks! > Try SELECT * FROM table ORDER BY LOWER(table_column); or of course SELECT * FROM table ORDER BY UPPER(table_column); Nick
you can do a lower on it to remove the case sensitivity. select * from table order by lower(table_column) asc -----Original Message----- From: Alexander Cohen [mailto:alex@toomuchspace.com] Sent: 31 March 2004 16:32 To: pgsql-general@postgresql.org Subject: [GENERAL] select statement sorting is it possible to have postgres return a "SELECT * FROM table ORDER BY table_column" query that is not case sensitive order? if i have these words in a column: Alex alex Barbara Cohen i will get them back in the following order in the tuples: Alex Barbara Cohen alex But i want them back likke this: Alex alex Barbara Cohen Is this possible or do i need to sort them myself after the query returns? Also, i noticed that postgres will let me create groups, databases and users with spaces in their names, is this ok or should i check this beforehand and not allow this? thanks! -- Alexander Cohen http://www.toomuchspace.com (819) 348-9237 (819) 432-3443 ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
On Wed, Mar 31, 2004 at 10:31:43 -0500, Alexander Cohen <alex@toomuchspace.com> wrote: > is it possible to have postgres return a "SELECT * FROM table ORDER BY > table_column" query that is not case sensitive order? > > if i have these words in a column: > > Alex > alex > Barbara > Cohen > > i will get them back in the following order in the tuples: > > Alex > Barbara > Cohen > alex > > But i want them back likke this: > > Alex > alex > Barbara > Cohen > > Is this possible or do i need to sort them myself after the query > returns? You can do an order by lower(table_column) if you don't care whether "Alex" or "alex" comes first. You can use a functional index on lower to speed this up if your table is large. > Also, i noticed that postgres will let me create groups, databases and > users with spaces in their names, is this ok or should i check this > beforehand and not allow this? They will work, but you will need to quote the names when you use them. My sugestion would be to use underlines instead of spaces in the names. I think that will be more readable than quoted names with blanks in them. If an application is creating these names on the fly using user input, I think you need to be very careful. In that case you probably shouldn't be using meaningful names but rather create names in a pattern that can't duplicate any other objects and keep a table with information about these objects with such things as the type, name and description.
On Wednesday 31 March 2004 16:31, Alexander Cohen wrote: > is it possible to have postgres return a "SELECT * FROM table ORDER BY > table_column" query that is not case sensitive order? SELECT first_name FROM foo ORDER BY lower(first_name) Of course, then you can't guarantee whether you get "Alex" then "alex" or the other way around, so you might want: SELECT first_name FROM foo ORDER BY lower(first_name), first_name > Also, i noticed that postgres will let me create groups, databases and > users with spaces in their names, is this ok or should i check this > beforehand and not allow this? You need to quote the names to create them this way. If you do so, you need to quote them when you use them, so: CREATE MyTable / SELECT FROM mytable/MYTABLE/MyTaBle... CREATE "MyTable" / SELECT FROM "MyTable" -- Richard Huxton Archonet Ltd
On Wed, Mar 31, 2004 at 10:31:43AM -0500, Alexander Cohen wrote: > is it possible to have postgres return a "SELECT * FROM table ORDER BY > table_column" query that is not case sensitive order? > Is this possible or do i need to sort them myself after the query > returns? SELECT * FROM table ORDER BY lower(table_column) will do what you want. [ If you start using this idiom for larger tables then remember that order by lower(something) won't take any advantage of an index on something - but may use a functional index on lower(something) ] > Also, i noticed that postgres will let me create groups, databases and > users with spaces in their names, is this ok or should i check this > beforehand and not allow this? I'd avoid it. It may well be acceptable within postgresql (I see no reason why it wouldn't be) but I'd bet that it'll confuse or break some third party tools. Cheers, Steve
I know this is an old topic, but it's not easy to find a way around it, so when we migrate SQL from other database to PostgreSQL, it causes a huge headache. Here's an extremely simple example - The original simple SQL - select distinct atcode from TMP order by torder; (it'll error out in PostgreSQL, although SQL92 extension may allow it; there's time you just can't do "select distinct atcode,torder from TMP order by torder"!!) My desire result - HGB HCT WBC RBC MCV MCH MCHC RDW RDWSD PLT DIFF | TYPE SEGS LYMPHS MONOS EOS BASOS I tried to rewrite the above simple query in PostgreSQL as - select distinct atcode from (select atcode,torder from TMP order by torder) t; But the return results are not what I want - BASOS DIFF | TYPE EOS HCT HGB LYMPHS MCH MCHC MCV MONOS PLT RBC RDW RDWSD SEGS WBC Can anybody provide a real/general solution to the above practical problem? (Tom?) This causes postgreSQL users too much time and headache. Thanks. johnl
--- John Liu <johnl@emrx.com> wrote: > I know this is an old topic, but it's not easy to > find a way around it, so > when we migrate SQL from other database to > PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct atcode from TMP order by torder; > > (it'll error out in PostgreSQL, although SQL92 > extension may allow it; > there's time you just can't do "select distinct > atcode,torder from TMP order > by torder"!!) > > My desire result - > HGB > HCT > WBC > RBC > MCV > MCH > MCHC > RDW > RDWSD > PLT > DIFF | TYPE > SEGS > LYMPHS > MONOS > EOS > BASOS What rule are you using to decide that order? If there are multiple values of torder for a given value of atcode, which of those values should be used for ordering? "DISTINCT ON", which is a PostgreSQL extension, may do what you want (depending on your answer to the above questions). Look at the "SELECT" page in the docs on "SQL Commands". > > I tried to rewrite the above simple query in > PostgreSQL as - select distinct > atcode from (select atcode,torder from TMP order by > torder) t; > > But the return results are not what I want - BASOS > DIFF | TYPE EOS HCT > HGB LYMPHS MCH MCHC MCV MONOS PLT RBC RDW > RDWSD SEGS WBC > > Can anybody provide a real/general solution to the > above practical problem? > (Tom?) This causes postgreSQL users too much time > and headache. > > Thanks. > johnl > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > > http://archives.postgresql.org------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org __________________________________ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html
"John Liu" <johnl@emrx.com> writes:
> The original simple SQL -
> select distinct atcode from TMP order by torder;
This is not "simple", it is "broken SQL with an undefined result".
If DISTINCT merges multiple rows with the same atcode, how are we
supposed to know which row's value of torder to sort the merged
row on?
Your other database was no doubt making a random choice and giving
you a random result ordering in consequence. You need to think harder
about what behavior you really want.
Once you can define the behavior (ie, just which torder you want to use)
you can probably implement it with something like
select atcode from
(select distinct on (atcode) atcode, torder from table
order by atcode, ???
) ss
order by torder;
where the ??? ordering determines which torder you get in each atcode group.
See the SELECT DISTINCT ON example in the SELECT reference page.
regards, tom lane
On Wednesday 31 March 2004 18:50, John Liu wrote: > I know this is an old topic, but it's not easy to find a way around it, so > when we migrate SQL from other database to PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct atcode from TMP order by torder; Can you explain what this means? If I have atcode | torder AAA | 20 BBB | 5 CCC | 10 BBB | 45 CCC | 27 What order should we get? You could argue for: 1. BBB,CCC,AAA since that is the order of the min(torder) 2. AAA,CCC,BBB since that is the order of the max(torder) 3. AAA,BBB,CCC if you take the first(torder) you find reading down the page 4. AAA,CCC,BBB if you take the first(torder) but read up the page Which one should PG pick, and how should it know? Which one did the other database pick, and why was it right to do so? -- Richard Huxton Archonet Ltd
On Wed, 31 Mar 2004, John Liu wrote: > I know this is an old topic, but it's not easy to find a way around it, so > when we migrate SQL from other database to PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct atcode from TMP order by torder; > > (it'll error out in PostgreSQL, although SQL92 extension may allow it; > there's time you just can't do "select distinct atcode,torder from TMP order > by torder"!!) > > I tried to rewrite the above simple query in PostgreSQL as - select distinct > atcode from (select atcode,torder from TMP order by torder) t; > > Can anybody provide a real/general solution to the above practical problem? > (Tom?) This causes postgreSQL users too much time and headache. Is atcode unique or can you assume that the torder values are the same for different rows of the same atcode? In general, I think something of the general form: select atcode from TMP group by atcode order by min(torder); may actually give results resembling what you want.
On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <johnl@emrx.com> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder; > > This is not "simple", it is "broken SQL with an undefined result". > > If DISTINCT merges multiple rows with the same atcode, how are we > supposed to know which row's value of torder to sort the merged > row on? > > Your other database was no doubt making a random choice and giving > you a random result ordering in consequence. You need to think harder > about what behavior you really want. > > Once you can define the behavior (ie, just which torder you want to use) > you can probably implement it with something like > > select atcode from > (select distinct on (atcode) atcode, torder from table > order by atcode, ??? > ) ss > order by torder; > > where the ??? ordering determines which torder you get in each atcode group. > See the SELECT DISTINCT ON example in the SELECT reference page. I did it like this: select atcode from table group by atcode order by max(toorder); Is that equivalent?
Thanks, scott.marlowe provides similar query. For this simple case the
result is the same. Here's another case, the result is different -
1. In the database allowing 'illegal distinct/w orderby not in the target
list'
select distinct drugname, drugid, encdate from CCMMed where pnum_site
='1913789_MC' order by drugname, encdate, mshdatetime desc;
drugname
drugid encdate
ALLOPURINOL
554 04/24/2000
ALLOPURINOL
554 05/14/2001
ALLOPURINOL
554 06/15/2001
ALLOPURINOL
554 08/20/2001
ALLOPURINOL
554 11/26/2001
ALLOPURINOL
554 05/22/2002
ALLOPURINOL
554 09/23/2002
ALLOPURINOL
554 01/13/2003
ALLOPURINOL
554 05/27/2003
ALLOPURINOL
554 09/29/2003
GLYBURIDE
1742 05/14/2001
GLYBURIDE
1742 06/15/2001
GLYBURIDE
1742 08/20/2001
GLYBURIDE
1742 11/26/2001
GLYBURIDE
1742 05/22/2002
GLYBURIDE
1742 09/23/2002
GLYBURIDE
1742 01/13/2003
GLYBURIDE
1742 05/27/2003
GLYBURIDE
1742 09/29/2003
2. In Pg, use your query group by then order by -
select drugname, drugid, encdate from ccmmed where pnum_site ='1913789_MC'
group by drugname, drugid, encdate order by max(mshdatetime);
drugname | drugid | encdate
-------------+--------+------------
ALLOPURINOL | 554 | 2000-04-24
ALLOPURINOL | 554 | 2001-05-14
GLYBURIDE | 1742 | 2001-05-14
GLYBURIDE | 1742 | 2001-06-15
ALLOPURINOL | 554 | 2001-06-15
ALLOPURINOL | 554 | 2001-08-20
GLYBURIDE | 1742 | 2001-08-20
GLYBURIDE | 1742 | 2001-11-26
ALLOPURINOL | 554 | 2001-11-26
ALLOPURINOL | 554 | 2002-05-22
GLYBURIDE | 1742 | 2002-05-22
GLYBURIDE | 1742 | 2002-09-23
ALLOPURINOL | 554 | 2002-09-23
ALLOPURINOL | 554 | 2003-01-13
GLYBURIDE | 1742 | 2003-01-13
GLYBURIDE | 1742 | 2003-05-27
ALLOPURINOL | 554 | 2003-05-27
ALLOPURINOL | 554 | 2003-09-29
GLYBURIDE | 1742 | 2003-09-29
3. My alternative in Pg for the above case -
select distinct drugname, drugid, encdate from (select drugname, drugid,
encdate, mshdatetime from CCMMed where pnum_site ='1913789_MC' order by
drugname, encdate, mshdatetime desc) t;
drugname | drugid | encdate
-------------+--------+------------
ALLOPURINOL | 554 | 2000-04-24
ALLOPURINOL | 554 | 2001-05-14
ALLOPURINOL | 554 | 2001-06-15
ALLOPURINOL | 554 | 2001-08-20
ALLOPURINOL | 554 | 2001-11-26
ALLOPURINOL | 554 | 2002-05-22
ALLOPURINOL | 554 | 2002-09-23
ALLOPURINOL | 554 | 2003-01-13
ALLOPURINOL | 554 | 2003-05-27
ALLOPURINOL | 554 | 2003-09-29
GLYBURIDE | 1742 | 2001-05-14
GLYBURIDE | 1742 | 2001-06-15
GLYBURIDE | 1742 | 2001-08-20
GLYBURIDE | 1742 | 2001-11-26
GLYBURIDE | 1742 | 2002-05-22
GLYBURIDE | 1742 | 2002-09-23
GLYBURIDE | 1742 | 2003-01-13
GLYBURIDE | 1742 | 2003-05-27
GLYBURIDE | 1742 | 2003-09-29
Note the same alternative approach for the simple query in my first post
email is not working in Pg.
Thanks.
johnl
-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone.bigpanda.com]
Sent: Wednesday, March 31, 2004 3:35 PM
To: John Liu
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] select distinct w/order by
On Wed, 31 Mar 2004, John Liu wrote:
> I know this is an old topic, but it's not easy to find a way around it, so
> when we migrate SQL from other database to PostgreSQL, it causes a huge
> headache. Here's an extremely simple example -
>
> The original simple SQL -
> select distinct atcode from TMP order by torder;
>
> (it'll error out in PostgreSQL, although SQL92 extension may allow it;
> there's time you just can't do "select distinct atcode,torder from TMP
order
> by torder"!!)
>
> I tried to rewrite the above simple query in PostgreSQL as - select
distinct
> atcode from (select atcode,torder from TMP order by torder) t;
>
> Can anybody provide a real/general solution to the above practical
problem?
> (Tom?) This causes postgreSQL users too much time and headache.
Is atcode unique or can you assume that the torder values are the same for
different rows of the same atcode?
In general, I think something of the general form:
select atcode from TMP group by atcode order by min(torder);
may actually give results resembling what you want.
On Thu, 1 Apr 2004, John Liu wrote: > 1. In the database allowing 'illegal distinct/w orderby not in the target > list' > select distinct drugname, drugid, encdate from CCMMed where pnum_site > ='1913789_MC' order by drugname, encdate, mshdatetime desc; > > 2. In Pg, use your query group by then order by - > select drugname, drugid, encdate from ccmmed where pnum_site ='1913789_MC' > group by drugname, drugid, encdate order by max(mshdatetime); This should be order by drugname, encdate, max(mshdatetime) to be equivalent to the above I would think.
Alexander Cohen wrote: > i will get them back in the following order in the tuples: > Alex > Barbara > Cohen > alex > > But i want them back likke this: > Alex > alex > Barbara > Cohen Set your locale to something other than C.
Tom provided the same logic arguments. I also like the way 'simple is better' as long as it sticks with SQL requirement. But in practice, you have to face such issue even it's 'catch 22' which depends on the application design - For the your case - code codeid AAA 20 BBB 5 CCC 10 BBB 45 CCC 27 When issue "select distinct code from test1 order by codeid;" One of the database returns using their internal rule (at least it's constant itself) - code BBB CCC AAA It provides one of the arguable result sets. But think about another situation, the result is for sure - code code2 codeid a1 a 1 a2 a 2 b1 d 3 b2 d 4 c1 c 5 c2 c 6 select distinct code2 from test2 order by codeid; code2 a d c It's handy. I hope everything is black or white, but it's not. The user has the choice at least. But when I use PostgreSQL, I need find an alternative solution to handle such issue. johnl -----Original Message----- From: Richard Huxton [mailto:dev@archonet.com] Sent: Wednesday, March 31, 2004 2:37 PM To: John Liu; pgsql-general@postgresql.org Subject: Re: [GENERAL] select distinct w/order by On Wednesday 31 March 2004 18:50, John Liu wrote: > I know this is an old topic, but it's not easy to find a way around it, so > when we migrate SQL from other database to PostgreSQL, it causes a huge > headache. Here's an extremely simple example - > > The original simple SQL - > select distinct atcode from TMP order by torder; Can you explain what this means? If I have atcode | torder AAA | 20 BBB | 5 CCC | 10 BBB | 45 CCC | 27 What order should we get? You could argue for: 1. BBB,CCC,AAA since that is the order of the min(torder) 2. AAA,CCC,BBB since that is the order of the max(torder) 3. AAA,BBB,CCC if you take the first(torder) you find reading down the page 4. AAA,CCC,BBB if you take the first(torder) but read up the page Which one should PG pick, and how should it know? Which one did the other database pick, and why was it right to do so? -- Richard Huxton Archonet Ltd
I don't think PG allows your query - select distinct atcode from tmp group by atcode order by max(torder); ERROR: For SELECT DISTINCT, ORDER BY expressions must appear in target list I can't make Tom's query work either - select atcode from (select distinct on (atcode) atcode, torder from tmp order by atcode, max(torder)) ss order by torder; ERROR: Attribute tmp.atcode must be GROUPed or used in an aggregate function Thanks for providing the alternative trials. johnl -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, March 31, 2004 4:35 PM To: Tom Lane Cc: John Liu; pgsql-general@postgresql.org Subject: Re: [GENERAL] select distinct w/order by On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <johnl@emrx.com> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder; > > This is not "simple", it is "broken SQL with an undefined result". > > If DISTINCT merges multiple rows with the same atcode, how are we > supposed to know which row's value of torder to sort the merged > row on? > > Your other database was no doubt making a random choice and giving > you a random result ordering in consequence. You need to think harder > about what behavior you really want. > > Once you can define the behavior (ie, just which torder you want to use) > you can probably implement it with something like > > select atcode from > (select distinct on (atcode) atcode, torder from table > order by atcode, ??? > ) ss > order by torder; > > where the ??? ordering determines which torder you get in each atcode group. > See the SELECT DISTINCT ON example in the SELECT reference page. I did it like this: select atcode from table group by atcode order by max(toorder); Is that equivalent?
Sorry, the query you provided works in Pg, my mistake :! select atcode from table group by atcode order by max(torder); johnl -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Wednesday, March 31, 2004 4:35 PM To: Tom Lane Cc: John Liu; pgsql-general@postgresql.org Subject: Re: [GENERAL] select distinct w/order by On Wed, 31 Mar 2004, Tom Lane wrote: > "John Liu" <johnl@emrx.com> writes: > > The original simple SQL - > > select distinct atcode from TMP order by torder; > > This is not "simple", it is "broken SQL with an undefined result". > > If DISTINCT merges multiple rows with the same atcode, how are we > supposed to know which row's value of torder to sort the merged > row on? > > Your other database was no doubt making a random choice and giving > you a random result ordering in consequence. You need to think harder > about what behavior you really want. > > Once you can define the behavior (ie, just which torder you want to use) > you can probably implement it with something like > > select atcode from > (select distinct on (atcode) atcode, torder from table > order by atcode, ??? > ) ss > order by torder; > > where the ??? ordering determines which torder you get in each atcode group. > See the SELECT DISTINCT ON example in the SELECT reference page. I did it like this: select atcode from table group by atcode order by max(toorder); Is that equivalent?