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
 New to SQL Server Programming
 Get one per ID

Author  Topic 

ugh3012
Yak Posting Veteran

62 Posts
Posted - 2014-06-03 : 12:46:08

It’s been a while since I post a question here, but I am stuck on one issue.

I have over 500 rows and many rows have different locations, so that means the total number of rows are much higher. What I am trying to figure out how to mass update one table using the below data, but with only one location. It does not matter which one.

Here is an example raw data.

ID | addr1 | City |....
5 | 203 N Main | Rochester
5 | 574 Church ST | Austin
5 | 512 Pocnoc | Lax
8 | 4535 Church | Lewistown
8 | 3423 Apple | Buda

Desired Result
ID | addr1 | City |....
5 | 203 N Main | Rochester
8 | 4535 Church | Lewistown

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2014-06-03 : 12:50:25
What version of SQL are you using? You might be able to make use of the ROW_NUMBER function:
SELECT
	ID,
	Addr1,
	City,
	...
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS RowNum
		FROM 
			TableName
	) AS Temp
WHERE
	Temp.RowNum = 1
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts
Posted - 2014-06-03 : 13:46:01
Thanks! That helped a lot. I have SQL Server 2008.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site