The UK Home Automation Archive

Archive Home
Group Home
Search Archive


Advanced Search

The UKHA-ARCHIVE IS CEASING OPERATIONS 31 DEC 2024


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

RE: [OT] SQL question


  • To: <ukha_d@xxxxxxx>
  • Subject: RE: [OT] SQL question
  • From: "Mark Hetherington" <mark.egroups@xxxxxxx>
  • Date: Fri, 22 Mar 2002 19:10:17 -0000
  • Delivered-to: mailing list ukha_d@xxxxxxx
  • Mailing-list: list ukha_d@xxxxxxx; contact ukha_d-owner@xxxxxxx
  • Reply-to: ukha_d@xxxxxxx

> Say this is the child data
>
> Parent Date       Comment
> 1      22/02/02   Some data
> 1      20/02/02   Some more data with the same parent
> 2      05/02/02   More data but for a different parent
> 2      01/02/02   More data for the second parent
> 3      20/02/02   Data for a third parent
>
> The result set I am after is this
> 1      22/02/02   Some data
> 2      05/02/02   More data but for a different parent
> 3      20/02/02   Data for a third parent
>
> IE the most receint comment only ?

This should do it:

SELECT DISTINCT ON (Parent) Parent,Date,Comment FROM child ORDER BY Date
DESC

It will basically sort all rows by date then select only the first row
(i.e.
the most recent entry). DESC is to ensure that we get the latest date at
the
top of the resulting table since SQL defaults to ASC.

HTH.

Mark.



Home | Main Index | Thread Index

Comments to the Webmaster are always welcomed, please use this contact form . Note that as this site is a mailing list archive, the Webmaster has no control over the contents of the messages. Comments about message content should be directed to the relevant mailing list.