Query for max value, multiple sets?

[rustytool]

Someone please help a rusty old hack. ;-)

I have the following situation that I have been unable
to work out a (SQL Server) query for:


TABLE:

RECORDID SERVICE TRANSACTION
======== ======= ===========
1000 1 3
1001 1 6
1002 100 1
1003 2 7
1004 5250 1
1005 100 2


I need a query that returns one row per each distinct
SERVICE where the TRANSACTION numberis the greatest for
that SERVICE.

In the example above, the results would be:

RECORDID SERVICE TRANSACTION
======== ======= ===========
1001 1 6
1003 2 7
1004 5250 1
1005 100 2

There are 1000's of SERVICE numbers and 10's of thousands
of RECORDID's.

Thanks,
Mac

 

[MillerH]

TI'm certain there there is probably a better method than this query, but it will work. Personally, I would just pull all the data and do the logic using a programming environment, or simply do two queries. But this will give you the results you want with a single query.

SELECT A.RECORDID, A.SERVICE, A.TRANSACTION
FROM yourTable A
LEFT JOIN yourTable B
ON B.SERVICE=A.SERVICE AND B.TRANSACTION>A.TRANSACTION
WHERE B.RECORDID IS NULL;

Note: You can speed up this queries performance greatly by having an index on (SERVICE, TRANSACTION).

 

[dnoeth]

SQL Server 2000:

select
RECORDID,
SERVICE,
TRANSACTION
from tab join
(select
SERVICE,
max(TRANSACTION) as maxtran
from tab
group by SERVICE
) dt
on tab.SERVICE = dt.SERVICE
and tab.TRANSACTION = dt.maxtran


SQL Server 2005 using SQL:1999 OLAP functions:

select
RECORDID,
SERVICE,
TRANSACTION
from
(select
RECORDID,
SERVICE,
TRANSACTION,
row_number() over (partition by SERVICE
order by TRANSACTION desc) as rn
) dt
where rn = 1

Dieter

 

[rustytool]

Thanks guys!

dnoeth's SQL Server 2000 query did the trick...