Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Distinct Recordset by Column Data

    I have a current recordset that is retuning like so:

    ID Name Number State
    1 Matt 1234 IN
    1 Matt 1234 TX
    2 Jeff 5678 WI
    2 Jeff 5678 IL


    I want it to return on of the two non-distinct rows but a distinct ID, for instance:

    ID Name Number State
    1 Matt 1234 IN
    2 Jeff 5678 WI

    It does not matter which record it returns. Does anyone have an idea
    on this?

    Thanks
    Matt

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Distinct Recordset by Column Data

    Select a.Id, a.Name, a.Number, a.State
    From TABLE a
    Where a.State = (Select Max(b.State)
    From Table b
    Where a.Id = b.Id)


    This will return the record where the state is the max for that record.

    ID Name Number State
    1 Matt 1234 TX
    2 Jeff 5678 WI

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •