Results 1 to 2 of 2

Thread: SQL Select only first unique row

  1. #1
    Join Date
    Oct 2008
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default SQL Select only first unique row

    I'm trying to build a query for mySQL to pull information from a database, but my issue is that I want to pull only the first unique value using a specific field as the index.
    The problem is, when I used the DISTINCT/GROUP BY trick, it returns the last unique value instead.

    For example in the following database:
    [ ID | Info ]
    [ 10 | Data 1 ]
    [ 10 | Data 2 ]
    [ 12 | Data 3 ]

    The output I want will be:
    [ ID | Info ]
    [ 10 | Data 1 ]
    [ 12 | Data 3 ]

    But instead I get:
    [ ID | Info ]
    [ 10 | Data 2 ]
    [ 12 | Data 3 ]

  2. #2
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,162
    Thanks
    263
    Thanked 690 Times in 678 Posts

    Default

    Without testing this to be sure, I believe you can always use ORDER BY `table` ASC (ascending) or DESC (descending) to specify the order.

    So based on your explanation, try:
    ..... ORDER BY `table` DESC

    (because ASC is usually the default-- if that returns the same results, switch to ASC)

    Alternatively you could of course just get all results, order by ASC/DESC as desired and remove duplicates with PHP:
    while ($row = mysql_fetch_assoc($queryresult)) {
    $output[$row['id']] = $row['info'];
    }
    That's not quite as efficient, but if you only have a few duplicates and not that much data overall it would be fine.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

Bookmarks

Posting Permissions

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