Get Full Row While Filtering for Duplicates on 2 Fields

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

SQL Server 2000 Forums

Transact-SQL (2000)

Get Full Row While Filtering for Duplicates on 2 Fields

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-11 : 09:32:32

Jake Horner writes "I have a table from which I need to pull distinct rows based on only 2 fields. Any duplicates should return only the most recent row.

In the example rows 2 & 3 have duplicate values for fields fld2 & fld3.

For Example:

TABLE FIELDS
fld1, fld2, fld3, fld3, fld4, fld5, myDate

DATA
Row 1- 11, 22, 33, 44, 55, '11/11/2005'
Row 2- 111, 222, 333, 444, 555, '4/1/2006'
Row 3- 1111, 222, 333, 4444, 5555, '4/3/2006'
Row 4- 11111, 2222, 3333, 44444, 55555, '1/1/2006'

DESIRED RESULT
11, 22, 33, 44, 55, '11/11/2005'
1111, 222, 333, 4444, 5555, '4/3/2006'
11111, 2222, 3333, 44444, 55555, '1/1/2006'

Thanks in advance for any help!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-11 : 09:45:07

Select fld1, fld2, fld3, fld3, fld4, fld5, Max(myDate) as mydate from yourtable
Group by fld1, fld2, fld3, fld3, fld4, fld5

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-11 : 12:58:01

Madhivanan - I'm guessing you missed the "based on only 2 fields" bit.

Jake - This does the trick. It's based on the old adage "first get the rows you need, then add in the columns"...

--data
set dateformat mdy
declare @t table (fld1 int, fld2 int, fld3 int, fld4 int, fld5 int, myDate datetime)

insert @t
          select 11, 22, 33, 44, 55, '11/11/2005'
union all select 111, 222, 333, 444, 555, '4/1/2006'
union all select 1111, 222, 333, 4444, 5555, '4/3/2006'
union all select 11111, 2222, 3333, 44444, 55555, '1/1/2006'

--calculation
select a.*
from @t a
    inner join (Select fld2, fld3, Max(myDate) as mydate from @t Group by fld2, fld3) b
        on a.fld2 = b.fld2 and a.fld3 = b.fld3 and a.myDate = b.myDate
order by a.fld1

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-12 : 05:36:35

Thanks Ryan

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources