#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Sorted DISTINCT with multiple fields returned?


    I have a table that contains the following fields

    id
    filename
    userid
    url
    timeaccessed

    The database will have many records that have the same filename, userid and url values. I need to output records (Order by timeaccessed DESC) displaying "filename" (with no repeats) and the "url" for records associated with userid # 303. The most recent records (by timeaccessed) need to display first.

    I tried:

    SELECT DISTINCT filename, url
    FROM TABLENAME
    WHERE userid = 303
    GROUP BY filename
    ORDER BY timeaccessed DESC ;

    The problem with the output here is that it doesn't display the most recent.

    In other words it selected the distinct filenames in ASC order and then displayed those in DESC order.

    ie:
    id filename userid url timeaccessed
    1 Test1 303 test1.htm 2013-5-12 08:10:45
    2 Test2 303 test2.htm 2013-5-12 08:12:28
    3 Test1 303 test1a.htm 2013-5-12 08:15:32
    4 Test3 303 test3.htm 2013-5-12 08:20:11
    5 Test2 303 test2a.htm 2013-5-12 08:32:06
    6 Test1 303 test1b.htm 2013-5-12 08:41:56

    The query would return
    Test3 test3.htm
    Test2 test2.htm
    Test1 test1.htm

    When I need it to return
    Test1 test1b.htm
    Test2 test2a.htm
    Test3 test2.htm
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,370
    Rep Power
    391
    Code:
    select filename,
           url
      from tablename
      join (select filename,
                   max(timeaccessed) as maxtime
              from tablename
             where userid = 303
             group
                by filename) dt
        on (tablename.filename,tablename.timeaccessed) = (dt.filename,dt.maxtime)
     where userid = 303
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Thank you so much that did it.

    I did have to change filename to TABLE.filename before it would work but now it does just fine.

IMN logo majestic logo threadwatch logo seochat tools logo