How to get last records in grouped query.

[schapopa]

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 ***

[schapopa]

Re: How to get last records in grouped query.

I have solution unless somebody has better one:
Create temp table with dates only, grouped by quarter and do right join
query on MyTable.

Create Table #temp1
(
IdTemp1 int not null Primary key identity ,
[date] datetime
)
Insert into #temp1
(
[date]
)

Select
Max(date)
from MyTable
group by Year([date]), DatePart (quarter,[date])
order by Year([date]), DatePart (quarter,[date])

select * from MyTable right join #temp1 on mytable.date = #temp1.date

*** Sent via Developersdex http://www.developersdex.com ***

[Roy Harvey]

Re: How to get last records in grouped query.

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

Roy Harvey
Beacon Falls, CT

On 23 Jan 2007 06:57:11 GMT, schapopa <schapopawrot e:

>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 ***