distinct/grouping problem

Question

distinct/grouping problem

tig2810

Valued Member

Points: 52
More actions
May 16, 2011 at 11:54 am

#250330

Hi all,
I have a table like below:
CODEDescriptionDateReferenceNo
OPENEDOpened2011-02-03110555
CHECKEDFile Checked2011-01-01110555
SENTFile Sent2011-03-09110555
RECEIVEDFile Received2011-04-01110555
SENTFile Sent2011-03-08110555
OPENEDOpened 2011-02-05110555
… lots more with different Reference numbers etc etc
If there are duplicate CODEs I need to return only the newest by date so it returns like below:
OPENEDOpened 2011-02-05110555
CHECKEDFile Checked2011-01-01110555
SENTFile Sent2011-03-09110555
RECEIVEDFile Received2011-04-01110555
I first tried the below
select max(CODE) as code,[description], date, ReferenceNo
from tableabove
where ReferenceNo = ‘110555’
group by CODE, company$$, [description], event_date, event_time, opsref$$
then tried a join to a subquery but this takes such a long time to return. ages! I have also tried using distinct but i am not getting anywhere.
Could someone help me with this as I am in a pickle!
Many thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Please check out the article in my signature and provide us some DDL and sample data. That makes our help that much easier to provide to you for free. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Try this:

; WIHT CTE AS

(

SELECT RN = ROW_NUMBER() OVER ( PARTITION BY ReferenceNo , CODE ORDER BY Date DESC )

, *

FROM CTE

)

SELECT * FROM CTE WHERE RN = 1

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

You didn't mention how big your table was (number of rows.)

To see some performance comparisons of different techniques dealing with a similar problem.

http://www.sqlservercentral.com/articles/T-SQL/69481/

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills