Removing Duplicates with condition - Help

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

General SQL Server Forums

Script Library

Removing Duplicates with condition - Help

Author

Topic

kanteti
Starting Member

5 Posts

Posted - 2009-02-12 : 06:57:43

I have following data

ACNO,Date,Nodename
1,2001-06-15,A1
2,2002-02-07,A2
3,2001-01-22,A3
1,2003-06-22,A1,
4,2007-12-22,A4
1,2008-01-23,A1
5,2000-11-23,A1

What result i am looking for is

ACNO,Date,Nodename
5,2000-11-23,A1
4,2007-12-22,A4
3,2001-01-22,A3
2,2002-02-07,A2
1,2008-01-23,A1(latest row on NodeName)

Please help on T-SQL.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-12 : 07:00:59

select t.*
from tbl t
where t.Date = (select max(Date) from tbl t2 where t2.Nodename = t.Nodename)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-02-12 : 07:02:39

Actually I think you might just want the latest Date for the ACNO,Nodename

select ACNO,Nodename, Date = max(Date)
from tbl
group by ACNO,Nodename

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kanteti
Starting Member

5 Posts

Posted - 2009-02-12 : 07:12:10

Hello Nr thank you very much for you help that works nicely in my subquery.

Thanks once again.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 07:21:20

[code]
Select ACNO,[Date],Nodename
from
(Select ROW_NUMBER() OVER(Partition by ACNO,Nodename order by [Date] desc) as ROW_ID,* from TABLE)Z
Where Z.ROW_ID = 1[/code]

kanteti
Starting Member

5 Posts

Posted - 2009-02-12 : 07:25:00

have following data

ACNO,Date,Nodename,Col4,Col5,.....,ColN
1,2001-06-15,A1
2,2002-02-07,A2
3,2001-01-22,A3
1,2003-06-22,A1,
4,2007-12-22,A4
1,2008-01-23,A1
5,2000-11-23,A1

What result i am looking for is

ACNO,Date,Nodename,Col4,Col5,.....,ColN
5,2000-11-23,A1
4,2007-12-22,A4
3,2001-01-22,A3
2,2002-02-07,A2
1,2008-01-23,A1(latest row on NodeName)

I have 50 Columns is there a way to avoid giving all in GROUP BY.

Please help on T-SQL.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 07:27:31

quote:
Originally posted by kanteti

have following data

ACNO,Date,Nodename,Col4,Col5,.....,ColN
1,2001-06-15,A1
2,2002-02-07,A2
3,2001-01-22,A3
1,2003-06-22,A1,
4,2007-12-22,A4
1,2008-01-23,A1
5,2000-11-23,A1

What result i am looking for is

ACNO,Date,Nodename,Col4,Col5,.....,ColN
5,2000-11-23,A1
4,2007-12-22,A4
3,2001-01-22,A3
2,2002-02-07,A2
1,2008-01-23,A1(latest row on NodeName)

I have 50 Columns is there a way to avoid giving all in GROUP BY.

Please help on T-SQL.

Thanks

Did you try mine if you are using SQL 2005? Do you really need all 50 columns?

kanteti
Starting Member

5 Posts

Posted - 2009-02-12 : 07:44:17

Hello actually all this queries are for PostGRESQL. I am not sure this will work Select ROW_NUMBER() OVER(Partition by ACNO,Nodename order by [Date] desc) as ROW_ID,* from TABLE)Z
Where Z.ROW_ID = 1 in PostGreSQL.

And on using all 50 Columns, yes we need all of them for our reports actually table has 150cols.

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 07:45:19

quote:
Originally posted by kanteti

Hello actually all this queries are for PostGRESQL. I am not sure this will work Select ROW_NUMBER() OVER(Partition by ACNO,Nodename order by [Date] desc) as ROW_ID,* from TABLE)Z
Where Z.ROW_ID = 1 in PostGreSQL.

And on using all 50 Columns, yes we need all of them for our reports actually table has 150cols.

Thanks

Then this forum is for SQL Server. You have to post somewhere else like www.dbforums.com

kanteti
Starting Member

5 Posts

Posted - 2009-02-12 : 08:34:09

select t.*
from tbl t
where t.Date = (select max(Date) from tbl t2 where t2.Nodename = t.Nodename)

Above query works perfect even in PostGreSQL. I did try this in dbforums but with no luck and PostGreSQL works quite well with generic SQL, i did give it a try and is worth it.

Thanks once again all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources