--- 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 ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
