ORDER BY help

Anon

Hello,
I have a table with three columns:

id (non-unique int)
string
time

What I want to return is the number of rows that match an id as well as the string of the most recent based on the time field. What I have so far is:

SELECT COUNT(id),string,time
FROM table
ORDER BY id

The problem is it returns the first string and time and I can't figure a way around this. I can get the last time using MAX(time) but the can't get the string with the greatest time. I'm not sure if what I want to do is possible but any suggestions are welcome.

Russell

totof06

What DB do you use ?

Christophe.
www.realscale.com

[deleted]

Try something like this:

SELECT COUNT(*),
id,
string,
time
FROM table
GROUP BY id,string
HAVING time = MAX(time)

Anon

That query returns an empty set as it seems HAVING time=MAX(time) only returns it if is is the greatest time, which it isn't. What I need is some way to order it by time before grouping it so it returns the string with the greatest time. Not sure if its possible but any ideas?

Russell

Anon

mysql

[deleted]

Euh?
HAVING time = MAX(time)
should return the row with the greatest 'time' value.
any change of a data-sample from your database to play around with?

Anon

Well here's what I'm running the query on:

id,group,user,time

1,1,Russell,20010826000000
2,1,Joe,20010827000000
3,1,Bob,20010828000000

SELECT user,time,COUNT(group)
FROM test
GROUP BY group

returns: Russell,20010826000000,3

SELECT user,time,COUNT(group)
FROM test
GROUP BY group
HAVING time=MAX(time)

returns: nothing

From what I got from the documentation for HAVING, it is applied after the grouping takes place and since the result time is the least of the times it is not returned.

Russell

totof06

I don't know if it works on mysql but it works with MSSQL Server:

DECLARE @ int

select @ = count(group)
from table
group by group

select @, users, time
from table
where time in (select max(time)
from table)

I don't think it's possible to do in only single query...

Hope it helps.

Christophe.
www.realscale.com