Post reply

distinct / top....???

  • October 12, 2010 at 2:51 am

    #229480

    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

  • October 12, 2010 at 3:25 am

    #1235366

    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.

  • October 12, 2010 at 3:28 am

    #1235367

    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 🙂

  • October 12, 2010 at 3:34 am

    #1235374

    Super - that's cracked it then.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply