SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 ROW_NUMBER vs GROUP BY
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

johnconstraint
Starting Member

Canada
23 Posts

Posted - 01/16/2013 :  13:50:25  Show Profile  Reply with Quote
I have a table with sample data shown below. I want to select only one row from the first two rows having identical Office_ID, similarly one row from 3rd and 4th rows. It doesn't matter which one is selected, all I need is just one row. All the five columns in the table constitute the primary key.

Date         ID           Office_ID          Office_ID2        Source
---------------------------------------------------------------------
20120930     12           123456789          7                 abd
20120930     12           123456789          7                 efg
20120930     12           901123456          7                 hik
20120930     12           901123456          7                 lmn

I was able to get the desired result using CTE as shown below. But the performance seems to be slow.

; WITH cte_tble AS (
SELECT Date,
ID,
Office_ID,
Office_ID2,
Source,
ROW_NUMBER () OVER (PARTITION BY Date, ID, Office_ID ORDER BY Source) AS Seq
FROM Table
)
select * from cte_tble 
where Seq =1 
and date_id = 20120930

So I am wondering if there is a better way of doing this, may be using Group by? Any help is appreciated.
I want to join the result set from the above selection to another table to get the final result.

Edited by - johnconstraint on 01/16/2013 13:54:45

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 01/16/2013 :  14:04:34  Show Profile  Reply with Quote
Here are my thoughts - but it is done with no data or testing to back them up, so.....

One possibility is to use an aggregate function as shown below. But, this has the problem that Office_ID2 may be picked from one row and Source might be picked from another row. If that is a problem, this would not work
SELECT Date,
	ID,
	Office_ID,
	MAX(Office_ID2) AS Office_ID2,
	MAX(Source) AS Source
FROM
	Tbl
GROUP BY
	Date,
	ID,
	Office_ID

Another possiblity which may speed up the subsequent queries that use this result set is to create a temp table as shown below and insert the data into that table (using your approach, or the one above) and then join with that temp table.

CREATE TABLE #data(Date DATETIME,
	ID INT NOT NULL,
	Office_ID INT NOT NULL,
	Office_ID2 INT NOT NULL ,
	Source VARCHAR(32)
	PRIMARY KEY CLUSTERED (Id, Office_ID, Office_ID2)
);
Go to Top of Page

johnconstraint
Starting Member

Canada
23 Posts

Posted - 01/16/2013 :  15:09:28  Show Profile  Reply with Quote
Thanks so much James K! I was able to make use of the first query that you provided.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000