Re: filtering groups
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/11/04
- Next message: Ibrahim Awwad: "Date Format"
- Previous message: novice: "filtering groups"
- In reply to: novice: "filtering groups"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 11 Aug 2004 20:43:35 +0200
On Wed, 11 Aug 2004 09:19:55 -0700, novice wrote:
>I need to write a query that will return the most recent
>incident within a related group, but I want a value
>returned for each instance of that group even if there's
>only one event recorded for them.
>
>Ex: The last transaction made by each buyer regardless of
>how many transactions they have made.
>
>I know this should be simple, but I'm not getting it
>right. Thanks.
Hi Novice,
Without knowing your table, data, etc, I can't really help you much. I can
give you an example how a similar problem is solved in another database.
The following query will return details of sales; for each store, only the
last sale will be displayed (note that if there were several sales on the
same day, they are all shown).
USE pubs
go
SELECT sales.stor_id, sales.ord_num, sales.ord_date, sales.qty
FROM sales
INNER JOIN (SELECT stor_id, MAX(ord_date)
FROM sales
GROUP BY stor_id) AS last_sale (stor_id, ord_date)
ON sales.stor_id = last_sale.stor_id
AND sales.ord_date = last_sale.ord_date
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Ibrahim Awwad: "Date Format"
- Previous message: novice: "filtering groups"
- In reply to: novice: "filtering groups"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
