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

distinct / top....??? Expand / Collapse
Author
Message
Posted Tuesday, October 12, 2010 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 10:52 AM
Points: 2, Visits: 4
Hi,

My data looks somethingl like this

UserID AnswerId Value
123 1 Mr
123 2 John
123 2 Micheal
123 3 Smith
123 3 Jones

What I would like to do is select back a distinct set of userid and answerids with either of the values where is is duplicated. So I should get back :

UserID AnswerId Value
123 1 Mr
123 2 John
123 3 Smith

I can't get my head round how to do it but know it is possible in one query..?

Any help would be appreciated

thanks
Post #1002611
Posted Tuesday, October 12, 2010 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:23 AM
Points: 112, Visits: 297
Do you mean you want John instead of Micheal because it appears "before" Micheal? If so, you are not in luck. Relational databases act on sets of data. There is no row order in a set - no row is before or after another. Think of a table containing rows like a bag full of balls.
Unless you can specify an order to the data then it is not possible to get the "top" row because there is no top row. If you could change this, say, to the alphabetically greatest name then it can be done.
Post #1002629
Posted Tuesday, October 12, 2010 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 10:52 AM
Points: 2, Visits: 4
Thanks, I was being dumb and have figured it out now by doing a MIN() on the Value column and using GROUP BY on the UserId and Answer column. I didn't mind which gets returned to be honest, I just completely forgot how to do it :)
Post #1002630
Posted Tuesday, October 12, 2010 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:23 AM
Points: 112, Visits: 297
Super - that's cracked it then.
Post #1002638
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse