0

Assuming a random dataset with an intentional Cartesian join, is it possible to extract a unique record for each username?

For example, my dataset (#t2):

UserName     City           State      Zip
bradley      Eagle Lake     TX         77434
bradley      Hudsonville    MI         49426
bradley      Egan           LA         70531
will756      Twin Rocks     PA         15960
will756      Palm           PA         18070
will756      Thornton       KY         41855

This was created with a Cartesian join. Now I want to select one record for bradley and one record for will756, and I don't care which record it is.

How would one do this?

For completeness sake, here is the SQL I wrote to get me where I am:

select top 10 percent row_number() OVER (ORDER BY tmpZipCodes.Place,tmpZipCodes.StateCode,tmpZipCodes.ZipCode) n,tmpZipCodes.Place as UserCity, tmpZipCodes.StateCode as UserState, tmpZipCodes.ZipCode as UserZip 
into #t1
from [tmpDropDowns]
inner join tmpZipCodes on tmpZipCodes.uStateCode = tmpdropdowns.StoredValue
where tmpdropdowns.DDLName = 'ddlState' and CountryCode = 'US'
order by newid()


select member_login.username as UserName, #t1.UserCity, #t1.UserState, #t1.UserZip 
into #t2
from member_login, #t1
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • if you're using sql 2008+ you can apply row_number to each row and partition by Username.. then just select all rows with row_number of 1 http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group – JamieD77 Feb 01 '16 at 20:32
  • Given the info above, how would I write that? – Johnny Bones Feb 01 '16 at 20:34
  • Actually, given that this is a Cartesian I'm thinking I'll end up with every username using the same City/State/Zip – Johnny Bones Feb 01 '16 at 20:35
  • You've already used row_number() in the first query. Why can't you adapt the solution in Jamie's comment? – Tab Alleman Feb 01 '16 at 20:36

0 Answers0