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
 Query to return entire row based upon two columns?

Author  Topic 

Ghost.Rider
Starting Member

4 Posts

Posted - 2013-08-12 : 18:03:01
I need help with a basic query.

I have a database that has a table that contains basic demographics

• First_name
• Last_name
• Address
• City
• State
• Zip phone
• email
• dob
• etc
• etc


Contained in this table are a lot of duplicate names, and even different addresses. I would like to return an entire row of UNIQUE information based upon “first_name + last name”. Now I know just enough of sql to pull out unique first + last names, but not the entire row based upon this unique information.

Can you help please?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-12 : 18:11:30
[code]SELECT * FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY Lastname,FirstName) AS RN
FROM YourTable
)s WHERE RN=1;[/code]The key thing to notice is the partition clause shown in red. It will give you one row per unique combination of whatever you put in there. The order by clause is something that I put in because I don't know any better. If you order them by City, for example, you will get the row with the first city.
Go to Top of Page

Ghost.Rider
Starting Member

4 Posts

Posted - 2013-08-13 : 12:04:24
Received the following error:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AS'.
Go to Top of Page

Ghost.Rider
Starting Member

4 Posts

Posted - 2013-08-13 : 12:06:13
Disregard. Fat-fingered it. Got it figured out! I never would have done this myself. Thanks!!!
Go to Top of Page

Ghost.Rider
Starting Member

4 Posts

Posted - 2013-08-13 : 12:19:48
Sop how to I mark topic as complete?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-13 : 12:56:06
SQLTeam forums do not have that concept. When activity in a thread trails off, people figure that either the person who asked the question has gone away disgusted with the whole thing, or got the problem resolved and is living happily ever after.
Go to Top of Page
   

- Advertisement -