duplicate rows with higher dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

duplicate rows with higher dates

Author

Topic

shebert
Yak Posting Veteran

85 Posts

Posted - 2006-08-23 : 13:59:00

Hello
lets say I have almost duplicate row

opnumber opname value date
179 test1 1000 8/1/2006
179 test1 2000 8/5/2006
179 test1 1500 8/22/2006

is there a simple query that will return the 8/22 values.

Thanks in advance
Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-08-23 : 14:10:05

What signifies the duplicate though? Just opnumber, or opnumber and opname?

For opnumber:


SELECT y.opnumber, y.opname, y.value, y.[date]
FROM YourTable y
INNER JOIN 
(
	SELECT MAX([date]) AS [date]
	FROM YourTable
	GROUP BY opnumber
) t
ON y.opnumber = t.opnumber AND y.[date] = t.[date]

For both:


SELECT y.opnumber, y.opname, y.value, y.[date]
FROM YourTable y
INNER JOIN 
(
	SELECT MAX([date]) AS [date]
	FROM YourTable
	GROUP BY opnumber, opname
) t
ON y.opnumber = t.opnumber AND y.opname = t.opname AND y.[date] = t.[date]

Alternatively:
SELECT opnumber, opname, value, [date]
FROM YourTable
WHERE [date] = (SELECT MAX([date]) AS [date] FROM YourTable GROUP BY opnumber)

I prefer the derived table approach though. The subquery approach requires that there aren't 2 or more rows with the same max date.

Tara Kizer

shebert
Yak Posting Veteran

85 Posts

Posted - 2006-08-23 : 14:35:02

BRILIANT THANKS SO MUCH.....

here what worked out

SELECT y.opportunitynumber, y.opportunityname, y.grossvalue, y.[startdate]
FROM @temp y
INNER JOIN
(
SELECT opportunitynumber,MAX([startdate]) AS [startdate]
FROM @temp
GROUP BY opportunitynumber
) t
ON y.opportunitynumber = t.opportunitynumber AND y.[startdate] = t.[startdate]

order by y.opportunitynumber,y.startdate

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources