Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Unanswered: Query for getting unique data (not as simple!)

    Hi,

    I have a table like so:

    Code:
    NAM  CAR  MOD
    Joe  Maz  Mil
    Ali  Bmw  650
    Sam  Bmw  330
    Pal  Toy  Cor
    Jim  Bmw  325
    Hoo  Frd  Foc
    Ken  Toy  Pri
    I want a query that returns a list of UNIQUE CARs AND the first NAM and MOD that correspond to it like so:

    Code:
    NAM  CAR  MOD
    Joe  Maz  Mil
    Ali  Bmw  650
    Pal  Toy  Cor
    Hoo  Frd  Foc
    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the concept of "first" does not exist in relational database tables, unless it is interpreted within the context of the values in some column

    so you can have the row with the lowest NAM for each car, or the row with the highest MOD for each car, or vice versa, but that pretty much exhausts your possibilities in this three column table

    of course, if it turns out that the table actually has more columns... ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ah so it's a unique list of MAKES not cars?!

    I'm curious to know why you would want the resultant data. The Model and Name seem to be completely irrelevant.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2006
    Posts
    28
    The above data were just for the sake of better demonstration. Anyways I've found the solution. I needed to group by CAR and use the keyword FIRST in front of MOD and NAM in my query

Posting Permissions

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