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.
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• etcContained 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. |
|
|
Ghost.Rider
Starting Member
4 Posts |
Posted - 2013-08-13 : 12:04:24
|
Received the following error:Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'AS'. |
|
|
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!!! |
|
|
Ghost.Rider
Starting Member
4 Posts |
Posted - 2013-08-13 : 12:19:48
|
Sop how to I mark topic as complete? |
|
|
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. |
|
|
|
|
|
|
|