Help with DISTINCT query - should be easy (noob)

Atari · #1 (**permalink**) 11-14-04, 21:35

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:

Quote:

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

r937 · #2 (**permalink**) 11-14-04, 23:22

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)

Atari · #3 (**permalink**) 11-14-04, 23:40

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.

r937 · #4 (**permalink**) 11-14-04, 23:44

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)

Atari · #5 (**permalink**) 11-14-04, 23:55

Yes, that's what I last tried - that query sends the server for a loop

r937 · #6 (**permalink**) 11-15-04, 00:17

do you have an index on time_accessed?

Internet Services

Web Development

Digital Marketing

Consumer Tech