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
 General SQL Server Forums
 New to SQL Server Programming
 Query to return entire row based upon two columns?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ghost.Rider
Starting Member

USA
4 Posts

Posted - 08/12/2013 :  18:03:01  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 08/12/2013 :  18:11:30  Show Profile  Reply with Quote
SELECT * FROM
(
	SELECT *,
	ROW_NUMBER() OVER (PARTITION BY LastName, FirstName ORDER BY Lastname,FirstName) AS RN
	FROM YourTable
)s WHERE RN=1;
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

USA
4 Posts

Posted - 08/13/2013 :  12:04:24  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 08/13/2013 :  12:06:13  Show Profile  Reply with Quote
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

USA
4 Posts

Posted - 08/13/2013 :  12:19:48  Show Profile  Reply with Quote
Sop how to I mark topic as complete?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 08/13/2013 :  12:56:06  Show Profile  Reply with Quote
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
  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