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/
Show quoted text
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 t1To 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/
Show quoted text
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