HELP wanted in advanced group by, - thanks.

Question

Post reply

HELP wanted in advanced group by, - thanks.

ckri

SSC Journeyman

Points: 78
More actions
December 6, 2011 at 3:21 am

#253025

Hi everybody - hope you can and will help.
This is an table with potentiel dublicates in MSLINK and LASTDATE
The SQL result must be unique on MSLINK, LASTDATE and OBS .... BUT only the newest LASTDATE is valid, and if LASTDATE is dubbed then OBS must be the one with the highest number.
Ex: in the two first rows only the first must be selected because it has the the highest OBS and LASTDATE is the same in the two rows.
This SQL results in 4 rows - which is one to many ... there should only be 3 rows:
SELECT mslink, lastdate, obs
FROM tvrapp
group by mslink,lastdate,obs
having lastdate in (select MAX(lastdate) from tvrapp group by mslink)
MSLINK RAPP LASTDATE FILMNAME OBS ID
10214112791995-02-02 00:00:00.000440-CA0010R-CA0020R.mpg2214422
102141TV_12791995-02-02 00:00:00.000CA0010R-CA0020R.mpg2116265
10694711691995-02-01 00:00:00.000433-CF1060F-CF1030F.mpg414302
10694711711995-02-01 00:00:00.000433-CF1030F-CF1060F.mpg1114304
1069471312082007-12-13 14:32:25.000FV2008JK14 14469
10694718120712007-12-18 20:57:09.000FV2008JK13 14826
106947TV_11651995-02-01 12:00:00.000CF1060F-CF1030F.mpg1616173
1070801131993-07-20 00:00:00.000304-DA1167F-DA1165F.mpg2314262
10708027080812008-08-27 00:00:00.0005F_27-08-2008_100108.mpg2615010
107080TV_113,1993-07-20 12:00:00.000DA1167F-DA1165F.mpg2116136
107080ISS56-2 2009-10-29 00:00:00.0000F_240A1110F_114933.mpg2317875
10708020091002010-09-20 00:00:00.0000F_20-09-2010_171925.mpg1720610
Best regards
Christian Risager

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

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Fairly trivial.

BEGIN TRAN

--First, lets create some sample data

SELECT MSLINK, RAPP, LASTDATE, FILMNAME, OBS, ID

INTO #tvrapp_SampleData

FROM (SELECT 102141, '1279', '1995-02-02 00:00:00.000', '440-CA0010R-CA0020R.mpg', 22, 14422

UNION ALL SELECT 102141, 'TV_1279', '1995-02-02 00:00:00.000', 'CA0010R-CA0020R.mpg', 21, 16265

UNION ALL SELECT 106947, '1169', '1995-02-01 00:00:00.000', '433-CF1060F-CF1030F.mpg', 4, 14302

UNION ALL SELECT 106947, '1171', '1995-02-01 00:00:00.000', '433-CF1030F-CF1060F.mpg', 11, 14304

UNION ALL SELECT 106947, '131208', '2007-12-13 14:32:25.000', 'FV2008JK', 14, 14469

UNION ALL SELECT 106947, '1812071', '2007-12-18 20:57:09.000', 'FV2008JK', 13, 14826

UNION ALL SELECT 106947, 'TV_1165', '1995-02-01 12:00:00.000', 'CF1060F-CF1030F.mpg', 16, 16173

UNION ALL SELECT 107080, '113', '1993-07-20 00:00:00.000', '304-DA1167F-DA1165F.mpg', 23, 14262

UNION ALL SELECT 107080, '2708081', '2008-08-27 00:00:00.000', '5F_27-08-2008_100108.mpg', 26, 15010

UNION ALL SELECT 107080, 'TV_113', '1993-07-20 12:00:00.000', 'DA1167F-DA1165F.mpg', 21, 16136

UNION ALL SELECT 107080, 'ISS56-2', '2009-10-29 00:00:00.000', '0F_240A1110F_114933.mpg', 23, 17875

UNION ALL SELECT 107080, '2009100', '2010-09-20 00:00:00.000', '0F_20-09-2010_171925.mpg', 17, 20610) a(MSLINK, RAPP, LASTDATE, FILMNAME, OBS, ID)

--Now lets filter out your results to the unique ones

SELECT MSLINK, RAPP, LASTDATE, FILMNAME, OBS

FROM (SELECT MSLINK, RAPP, LASTDATE, FILMNAME, OBS, ID,

ROW_NUMBER () OVER (PARTITION BY MSLINK ORDER BY LASTDATE DESC, OBS ASC) AS rn

FROM #tvrapp_SampleData) a

WHERE a.rn = 1

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ckri SSC Journeyman Points: 78 More actions · Answer 2

that was quick - thanks a lot and thanks for introducing PARTITION

but I'm not sure ....

it seems to display the wrong row in example .... the row with 21 obs, - even if I replace ASC with DESC???

regards

chr

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

ckri (12/6/2011)
that was quick - thanks a lot and thanks for introducing PARTITION
but I'm not sure ....
it seems to display the wrong row in example .... the row with 21 obs, - even if I replace ASC with DESC???
regards
chr

Sorry, was it supposed to be the OBS with the highest number? I assumed it was the most recent date and the lowest OBS.

OK, here's the corrected version which displays the highest OBs and most recent date.