Getting the Non Aggregate value in A Group By TSQL with ou..

Hi,

Please help me how to get the Non Aggregate value in A Group By TSQL with
out Aggr Function.

Eg:

create table #temp(rng int,marketdate int,price float)
go
insert #temp values(0,20060821, 79.87999)
insert #temp values(0,20060822, 79.549988)
insert #temp values(0,20060823, 79.469986)
insert #temp values(0,20060824, 79.5)
insert #temp values(0,20060825, 80)

select max(marketdate) from #temp group by rng

I need the corresponding Price for that marketdate. I have millions of
records in this table. Please help me this query.

One way i can get is by using a derrived/temp table for above and join it
with #temp and get the corresponding price. Please let me know , if there is
any other way to do it.

Appreciate you help!!!

sp (sp@discussions.microsoft.com) writes:
> Please help me how to get the Non Aggregate value in A Group By TSQL with
> out Aggr Function.
>
> Eg:
>
> create table #temp(rng int,marketdate int,price float)
> go
> insert #temp values(0,20060821, 79.87999)
> insert #temp values(0,20060822, 79.549988)
> insert #temp values(0,20060823, 79.469986)
> insert #temp values(0,20060824, 79.5)
> insert #temp values(0,20060825, 80)
>
> select max(marketdate) from #temp group by rng
>
> I need the corresponding Price for that marketdate. I have millions of
> records in this table. Please help me this query.
>
> One way i can get is by using a derrived/temp table for above and join
> it with #temp and get the corresponding price. Please let me know , if
> there is any other way to do it.

The most efficient way to do it, is likely to be this:

WITH numbereddates AS (
SELECT rng, marketdate, price,
rowno = row_number() OVER(PARTITION BY rng ORDER BY marketdate
DESC)
FROM #temp
)
SELECT rng, marketdate, price
FROM numbereddates
WHERE rowno = 1


But you need SQL 2005 for this; the query dows not run on SQL 2000.



--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx