Re: select distinct w/order by

--- John Liu <johnl(at)emrx(dot)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