select statement sorting

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 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

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

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

Don't know why this is not posted ...

-----Original Message-----
From: John Liu [mailto:johnl@emrx.com]
Sent: Wednesday, March 31, 2004 11:50 AM
To: 'pgsql-general@postgresql.org'
Subject: select distinct w/order by

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> 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

--- 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

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

Not that this is the issue, but what kind of tool where you using to get
your results back with this "other" database? Sometimes these fancy GUI
tools like to be smart on you and order things based on something it feels
is correct giving you the impression that the database choose the order
when infact the GUI tool choose the order. Just a thought

Bob Henkel 651-738-5085
Mutual Funds I/T Woodbury
Hartford Life
500 Bielenberg Drive
Woodbury, MN 55125

|---------+---------------------------------->
| | Richard Huxton |
| | <dev@archonet.com> |
| | Sent by: |
| | pgsql-general-owner@pos|
| | tgresql.org |
| | |
| | |
| | 03/31/2004 02:37 PM |
| | |
|---------+---------------------------------->

------------------------------------------------------------------------------------------------------------------------------|

| |
| To: "John Liu" <johnl@emrx.com>, <pgsql-general@postgresql.org> |
| cc: |
| 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

*************************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is for the exclusive use of addressee and may contain proprietary, confidential and/or privileged information. If you are not the intended recipient, any use, copying, disclosure, dissemination or distribution is strictly prohibited. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this communication and destroy all copies.
*************************************************************************

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

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?

On Wednesday 31 March 2004 22:13, John Liu wrote:

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 -

Tom's a smart fella, of course he agreed with me ;-)
I'd argue PG does deal with the issue, by refusing to do handle an unsafe
situation. The "catch 22" only happens if a database supports vague queries.

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

Are you sure it's consistent? If you didn't know which results you were going
to get before testing it, how do you know it's right. Maybe when you tested
it, perhaps you got lucky. And, if the "theoretical" arguments don't convince
you, here's something fairly practical. If the behaviour isn't defined, and
it just happens to work this way, what guarantee will you have that another
database, or event the next version of your current one will give you the
same order?

It provides one of the arguable result sets.

PG could provide one, but which one, and why should that be the right choice?

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.

You can't have a feature that only works for some cases. In your example,
there are no overlapping codeid ranges on any given code2. This means you can
use either of the min/max sorts I mentioned. PG needs to know what "order by
codeid" means.

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.

Everything is black and white. You should be able to run the same queries on
the same data in any two databases and get the same results. If you define
your query correctly, that should be the case. (Note it's not your fault the
query is poorly defined, at first glance it looks like a sensible thing to
do. The fact is though, that it's not and the other database shouldn't let
you do it).

Below is a real example from one of my projects - both orders are valid, but
they give different results. You will need to choose one.

promise=> SELECT gal_cnttype FROM lkp_gallery GROUP BY gal_cnttype ORDER BY
min(gal_code);
gal_cnttype
-------------
CLILOGO
MMS
OPPLOGO
PICMSG
MONO
POLY
JAVA
BUNDLE
AISCRIPT
(9 rows)

promise=> SELECT gal_cnttype FROM lkp_gallery GROUP BY gal_cnttype ORDER BY
max(gal_code);
gal_cnttype
-------------
BUNDLE
JAVA
MONO
POLY
AISCRIPT
CLILOGO
MMS
OPPLOGO
PICMSG
(9 rows)

--
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?

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.