Re: Displaying items in a table according to the earliest date found in a column

On 21 Jan 2005 08:07:01 -0800, nathan.cutmore_at_burohappold.com wrote:

>Hello again
>
>
>I am trying to develop an SQL query that turns this table...
>
>----------------------------------------------------------------
>item_id item_title location_id location dueback
>
>----------------------------------------------------------------
>476 Abfab 2 Dubai 01/04/05
>476 Abfab 6 London 23/07/05
>476 Abfab 6 London 17/08/05
>476 Abfab 6 London 20/08/05
>476 Abfab 3 Paris 12/05/05
>----------------------------------------------------------------
(snip)
>Into this (where each location is shown but only with the row
>that has earliest 'dueback' date) ...
>
>----------------------------------------------------------------
>item_id item_title location_id location dueback
>
>----------------------------------------------------------------
>476 Abfab 2 Dubai 01/04/05
>476 Abfab 6 London 23/07/05
>476 Abfab 3 Paris 12/05/05
>
>----------------------------------------------------------------

Hi Nathan,

SELECT item_id, item_title, location_id, location, MIN(dueback) FROM thistable
GROUP BY item_id, item_title, location_id, location

By the way: if the above is the actual content of one table, I suggest you to pick up a good book about database design and normalization first. Disregard this remark if it's the output of a query.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)