[GENERAL] SQL question.

[FC]

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])