Search Postgresql Archives

Re: SQL question.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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 <lne-1mc8@xxxxxxxxxxx>
To: pgsql-general@xxxxxxxxxxxxxx
Sent: Wed, 1 Jun 2005 16:40:48 +0200
Subject:  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 majordomo@xxxxxxxxxxxxxx)

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





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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux