Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

unique records based on one column Expand / Collapse
Author
Message
Posted Friday, January 7, 2011 8:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 1,749, Visits: 3,153
I have a query that pulls parent login info and insert into another table.

I want to select unique records based on uique ParentEmail column.

Because we use ParentEMail as login name they have to be unique.

So my select query has below columns:
select familyID, ParentEmail, Parentpassword, Adusername, Adpassword, firstname, lastname, nameID
from tblParents

How can I modify the above query, that if there are duplicated Parent email, I will only pull the first record so this way, all the records will be unique based on ParentEmail column?

Thanks
Post #1044464
Posted Friday, January 7, 2011 9:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
;with CTE as
(select familyID, ParentEmail, Parentpassword, Adusername, Adpassword, firstname, lastname, nameID,
row_number() over (partition by ParentEmail order by familyID) as Row
from tblParents)
select *
from CTE
where Row = 1;

Will that give you what you need?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #1044490
Posted Friday, January 7, 2011 9:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:36 AM
Points: 539, Visits: 275
Yeah, that should give the desired result.
Post #1044495
Posted Friday, January 7, 2011 9:30 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:26 AM
Points: 1,749, Visits: 3,153
Thank you, that's exactly what I want.

Post #1044505
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse