From: | FC <lne-1mc8(at)myamail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SQL question. |
Date: | 2005-06-01 14:40:48 |
Message-ID: | 6EE9301F-4C50-4160-9DC0-667826764A74@myamail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Dan Black | 2005-06-01 14:48:52 | Re: Missing numbers |
Previous Message | John DeSoi | 2005-06-01 14:20:37 | Re: Newby to DB |