Posted by Roy Harvey on 01/23/07 13:41

SELECT A.Value, B.Yr, B.Qtr
FROM MyTable as A
JOIN (SELECT Yr = datepart(year, date),
Qtr = datepart(quarter, date),
max(date) as LastDate
FROM MyTable
GROUP BY datepart(year, date),
datepart(quarter, date)) as B
ON A.date = B.LastDate

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopa> wrote:

>Hi,
>I want to create query where I could group records by quarters, and get
>the last record in each group.
>
>e.g
>Create Table MyTable
>(
>Value [float],
>date[datetime]
>)
>Insert into MyTable (value, [date]) values (100, '1-1-2000')
>Insert into MyTable (value, [date]) values (110, '1-2-2000')
>Insert into MyTable (value, [date]) values (120, '1-3-2000')
>Insert into MyTable (value, [date]) values (130, '1-4-2000')
>Insert into MyTable (value, [date]) values (140, '1-5-2000')
>Insert into MyTable (value, [date]) values (150, '1-6-2000')
>Insert into MyTable (value, [date]) values (160, '1-7-2000')
>
>Now I would like to get this data grouped by quarter, and get the last
>value from each quarter. So here I would like to get result like this
>(120, q1 -2000)
>(150, q2 -2000)
>(160, q3 -2000)
>I know how to create aggregate functions but I have problem with getting
>that last record from each group.
>
>
>
>*** Sent via Developersdex http://www.developersdex.com ***

• Next in forum: list all tables in master
• Prev in forum: Re: Datawarehousing/Business Intelligence Training Class
• Thread view: Re: How to get last records in grouped query.

[Reply to this message]