Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: Help with DISTINCT query - should be easy (noob)

    Hello,

    I have a table with three columns of interest:

    type int(1)
    value int(3)
    time_accessed timestamp(14)

    Here is some sample data:

    type, value, time_accessed
    1, 3, 20040611154031
    0, 3, 20040611154000
    0, 533, 20040601154031
    1, 533, 20030611154031
    Now, what I'd like to have, is a single query, that returns DISTINCT 'value', and only latest type per value, ex return:

    type, value:
    1, 3
    0, 533

    Anyone know how this is done? My instinct was:

    SELECT DISTINCT value, type FROM table_a ORDER BY time_accessed DESC;

    The problem however, is that this returns each 'type' entry..

    Help appreciated!
    A

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select t1.type
         , t1.value
      from table_a as t1
    inner
      join table_a as t2
        on t1.value
         = t2.value
    group
        by t1.type
         , t1.value
    having t1.time_accessed
         = max(t2.time_accessed)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Posts
    35
    Wow... I must learn GROUP BY and HAVING

    The above gives me this error:
    ERROR 1054: Unknown column 't1.time_accessed' in 'having clause'

    My intuition would have me add t1.time_accessed to the SELECT clause, but then that makes the query take centuries. I've not been patient enough to wait for a return. The table has only 1600 rows.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry

    you ahve to add it to both the SELECT and the GROUP BY
    Code:
    select t1.type
         , t1.value
         , t1.time_accessed
      from table_a as t1
    inner
      join table_a as t2
        on t1.value
         = t2.value
    group
        by t1.type
         , t1.value
         , t1.time_accessed
    having t1.time_accessed
         = max(t2.time_accessed)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    35
    Yes, that's what I last tried - that query sends the server for a loop

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you have an index on time_accessed?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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