SQLTeam.com | Weblogs | Forums

How to get the last occurrence of a field

Hi,

I have a simple query that returns two fields. I need to modify that query so that it only returns the last or highest number for each pair of fields.

It's easier to show than explain. Here's my query:

SELECT WONum, OpNum
FROM  tblHistory
ORDER BY WONum, OpNum

It returns something like this:

WONum OpNum
1396026 40.00
1396026 90.00
1396026 90.00
1396026 120.00
1427600 130.00
1456392 120.00
1456392 130.00
1458018 230.00
1458018 280.00
1460224 40.00
1460224 80.00

I need it to return:
WONum OpNum
1396026 120.00
1427600 130.00
1456392 130.00
1458018 280.00
1460224 80.00

Just the last row of each WONum, OpNum pair

select top 1 WONum,OpNum
FROM  tblHistory
order by WONum desc, OpNum desc
drop table #SampleData 
create table #Sampledata 
(
WONum int , OpNum int 
)
insert into #Sampledata select 1396026,40
insert into #Sampledata select 1396026,90
insert into #Sampledata select 1396026,90
insert into #Sampledata select 1396026,120
insert into #Sampledata select 1427600,130
insert into #Sampledata select 1456392,120
insert into #Sampledata select 1456392,130
insert into #Sampledata select 1458018,230
insert into #Sampledata select 1458018,280
insert into #Sampledata select 1460224,40
insert into #Sampledata select 1460224,80

select 'Sample Data ', * from #SampleData 

select 	'SQL Output',WONum ,max(OpNum) from #Sampledata group by WONum

image