selecting 1 row from kind of duplicates

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 2008 Forums

Transact-SQL (2008)

selecting 1 row from kind of duplicates

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-03-19 : 07:54:56

Hi,

I have a table which has millions of rows. The below is just an example.

create table #temp1
( app nvarchar(100),
version nvarchar(100),
region nvarchar(100),
city nvarchar(100),
scandate datetime)

insert into #temp1 values('adobe','7.0','nam','texas','2012-02-13 05:25:42.000')
insert into #temp1 values('adobe','7.0','aspac','texas','2012-02-17 08:58:06.000')
insert into #temp1 values('adobe','7.0','emea','texas','2012-02-12 22:22:17.000')
insert into #temp1 values('excel','2000','aspac','texas','2012-02-10 15:31:34')
insert into #temp1 values('excel','2000','emea','texas','2012-02-10 15:31:34')

This is what I need:
for the same "app" and "version", i need one row based on max "scandate".

so my output will be.

adobe 7.0 aspac texas 2012-02-17 08:58:06.000
excel 2000 aspac texas 2012-02-10 15:31:34.000

OR

adobe 7.0 aspac texas 2012-02-17 08:58:06.000
excel 2000 emea texas 2012-02-10 15:31:34.000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-19 : 08:39:37

Theoretically, the following should work. However, if you have millions of rows, it is likely to be slow. Other approaches that I can think of would be equally slow because of the presence of multiple rows with the same scandate. Before you get dejected, let us see if someone else would post a better solution.

SELECT
	app, [version],region,city, scandate
FROM
(
	SELECT *,ROW_NUMBER() OVER (PARTITION BY app,version ORDER BY scandate DESC) AS RN
	FROM #temp1
)s  WHERE RN = 1;

sachingovekar
Posting Yak Master

101 Posts

Posted - 2012-03-20 : 10:03:32

Thanks Sunita. It looks like a good solution.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources