www.webdeveloper.com
Results 1 to 3 of 3

Thread: Select one row with a distinct column, ordered by another column..

  1. #1
    Join Date
    Nov 2010
    Posts
    53

    Select one row with a distinct column, ordered by another column..

    Hi,

    I am trying to make a simple search where I can look up orders. The thing that's getting me is I want only one result for each unique 'client_name', and it has to be the most recent row (I have a date column to order by).

    So if I have the following (the date is yymmdd):

    name | quantity | order_date | details

    Tim J | 20 | 130720 | detail1
    Tim J | 40 | 130501 | detail2
    Tim A | 60 | 120608 | detail3
    Tim K | 12 | 130625 | detail4
    Tim K | 100 | 111208 | detail5
    Tim A | 100 | 111208 | detail6

    And I search for Tim, I want it to return 1 row (all columns) for each distinct name but it has to be the most recent row.

    So I'd get

    Tim J | 20 | 130720 | detail1
    Tim A | 60 | 120608 | detail3
    Tim K | 12 | 130625 | detail4

    I have tried SELECT DISTINCT and GROUP BY (which I don't really understand) but have so far failed..
    The searching is under control using WHERE client_name LIKE 'my_search_box'..

    I feel this should be so much easier than it seems.. thanks in advance!

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,154
    Possibly not highly efficient:
    Code:
    select * from table_name as tn
    where order_date = (
      select order_date from table_name
      where name = tn.name
      order by order_date desc limit 1 -- how we get the latest one
    )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Nov 2010
    Posts
    53
    Quote Originally Posted by NogDog View Post
    Possibly not highly efficient:
    Code:
    select * from table_name as tn
    where order_date = (
      select order_date from table_name
      where name = tn.name
      order by order_date desc limit 1 -- how we get the latest one
    )
    Thanks heaps man, it works!
    The logic is a little confusing but that's ok :P

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles