I need to remember to keep things simple...

Works fine, thanks.

Fred


On Jun 1, 2005, at 5:20 PM, Jim Buttafuoco wrote:

how about (untested)

select *
from
(
SELECT DISTINCT ON (linkid) *
FROM all_links
WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC ('day',TIMESTAMP '2005-06-01')
ORDER BY linkid
) A
ORDER BY read_date DESC limit 100


---------- Original Message -----------
From: FC <[EMAIL PROTECTED]>
To: pgsql-general@postgresql.org
Sent: Wed, 1 Jun 2005 16:40:48 +0200
Subject: [GENERAL] SQL question.


Hello SQL Aces !

I want to do a select on a table distinct on linkid and sorted by
date. I have try this

SELECT DISTINCT ON (linkid) * FROM all_links
   WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
         TIMESTAMP '2005-06-01') ORDER BY linkid, read_date;

With this request "works" but the raws are sorted as I want. To have
the rows sorted by date I have done this using a temporary table and
that works exactly as I want.

CREATE LOCAL TEMP TABLE temp_links WITHOUT OIDS AS
   SELECT DISTINCT ON (linkid) * FROM all_links
   WHERE uid='2' AND DATE_TRUNC('day',read_date) = DATE_TRUNC('day',
         TIMESTAMP '2005-06-01') ORDER BY linkid;

SELECT * from temp_links ORDER BY read_date DESC limit 100

My question is... How can I do the same thing in the more efficient
way and without using a temporary table. Since I am using PHP and the
table is not deleted at the end of the program because PHP keeps the
connection to the database open.

Thanks for any suggestions.
Fred

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

------- End of Original Message -------





---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly

Reply via email to