Thread: Formulating an sql query with CTID
Hi, I have a query i am having trouble to formulate: I used to do: SELECT DISTINCT a0,a1 FROM public.t1 However, now i need to add the CTID attribute, but CTID is unique thus, distinct is useless. In addition, i can't seem to be able to use max() etc... or casting to anything on CTID in order to use group by technique. What are my options? (I am using 8.2 or 8.1) -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html
10x, Found my answer thanks to Ragnar. SELECT DISTINCT ON (a0,a1) a0,a1,ctid FROM t1 To tell the truth, i did not know there was an ON option in SQL. This thing is very handy. On Saturday 29 July 2006 03:37, Tzahi Fadida wrote: > Hi, > I have a query i am having trouble to formulate: > I used to do: > SELECT DISTINCT a0,a1 FROM public.t1 > However, now i need to add the CTID attribute, but CTID is unique > thus, distinct is useless. In addition, i can't seem to be able to use > max() etc... or casting to anything on CTID in order to use group by > technique. > > What are my options? > > (I am using 8.2 or 8.1) -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html
On Sat, Jul 29, 2006 at 03:37:15AM +0300, Tzahi Fadida wrote: > Hi, > I have a query i am having trouble to formulate: > I used to do: > SELECT DISTINCT a0,a1 FROM public.t1 > However, now i need to add the CTID attribute, but CTID is unique > thus, distinct is useless. In addition, i can't seem to be able to use max() > etc... or casting to anything on CTID in order to use group by technique. Use DISTINCT ON, see the docs for details. http://www.postgresql.org/docs/7.4/static/queries-select-lists.html That's for an older version, but it still works the same, google isn't delivering the newer version... Have an nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
On Sat, Jul 29, 2006 at 01:37:44PM +0300, Tzahi Fadida wrote: > 10x, > Found my answer thanks to Ragnar. > SELECT DISTINCT ON (a0,a1) a0,a1,ctid FROM t1 > > To tell the truth, i did not know there was an ON option in SQL. > This thing is very handy. There isn't, it's a postgresql extension, albeit a very very useful one. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Ühel kenal päeval, L, 2006-07-29 kell 18:25, kirjutas Martijn van Oosterhout: > On Sat, Jul 29, 2006 at 03:37:15AM +0300, Tzahi Fadida wrote: > > Hi, > > I have a query i am having trouble to formulate: > > I used to do: > > SELECT DISTINCT a0,a1 FROM public.t1 > > However, now i need to add the CTID attribute, but CTID is unique > > thus, distinct is useless. In addition, i can't seem to be able to use max() > > etc... or casting to anything on CTID in order to use group by technique. > > Use DISTINCT ON, see the docs for details. > > http://www.postgresql.org/docs/7.4/static/queries-select-lists.html > > That's for an older version, but it still works the same, google isn't > delivering the newer version... replace /7.4/ with /8.1/ to get a newer version ;) > Have an nice day, -- ---------------- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com