need help ordering a GROUP BY query

[5kyy8lu3]

Hi.  I have MySQL 5.0.45.

 

My table looks kinda like this: (fake table, and i actually use real DATETIME, just too lazy to make fake ones for this example)

 

ID - Timestamp ------ IP --------- Browser

 

1  - today ------65.45.54.45------firefox 3.5.2

2 - yesterday---101.101.1.0-------IE 6.0

3 - last week----65.45.54.45 -----firefox 3.0.1

 

as you can see, the first and third entries have the same IP address.

 

i want my ip log to display the last 10 ip's logged, newest first (ORDER BY Timestamp DESC)

 

for the other information like Browser, I want pulled from the most Recent entry.

 

this is what I want the results to be like:

 

Today ------ 65.45.54.45 - 2 hits - firefox 3.5.2

Yesterday -- 101.101.1.0 - 1 hit -- IE 6.0

 

so... here's what I thought the query should be like:

 

SELECT ID, Timestamp, IP, Browser, COUNT(IP) GROUP BY IP ORDER BY Timestamp DESC

 

but that returns this:

 

Last Week -- 65.45.54.45 - 2 hits - firefox 3.0.1

Yesterday -- 101.101.1.0 - 1 hit --- IE 6.0

 

it groups up the IP's like it's supposed to, and COUNT(IP) returns the correct number of "hits" for that IP, but the other info for that IP are pulled from the oldest entry, which is the opposite of what I'm wanting, I'm wanting it to pull the Timestamp and Browser from the most recent hit.

 

I'm not sure that made sense but I hope my fake table and queries helped a little.

 

Thanks ahead of time.

[kickstart]

Hi

 

Think you will have to do a JOIN with a subselect to get the max details.

 

SELECT a.ID, b.MaxTimestamp, b.IP, a.Browser, b.IpCount
FROM SomeTable a
INNER JOIN (SELECT IP, MAX(Timestamp) AS MaxTimestamp, COUNT(Timestamp) AS IpCount FROM SomeTable GROUP BY IP ORDER BY MaxTimestamp LIMIT 10) b
ON a.IP = b.IP AND a.Timestamp = b.MaxTimestamp

 

With a GROUP BY you should specify all the non aggregate fields that you are grouping on. If you don't then a random one will be returned (or with most flavours of SQL the SQL will not run).

 

All the best

 

Keith

 

 

[5kyy8lu3]

Hi

 

Think you will have to do a JOIN with a subselect to get the max details.

 

SELECT a.ID, b.MaxTimestamp, b.IP, a.Browser, b.IpCount
FROM SomeTable a
INNER JOIN (SELECT IP, MAX(Timestamp) AS MaxTimestamp, COUNT(Timestamp) AS IpCount FROM SomeTable GROUP BY IP ORDER BY MaxTimestamp LIMIT 10) b
ON a.IP = b.IP AND a.Timestamp = b.MaxTimestamp

 

With a GROUP BY you should specify all the non aggregate fields that you are grouping on. If you don't then a random one will be returned (or with most flavours of SQL the SQL will not run).

 

All the best

 

Keith

cool thanks, i'll give it a shot.  that's alot more complex than i was thinking the solution would be.  i guess i should go finish reading my book.

 

thanks =)

[kickstart]

Hi

 

It would be safer and faster if the ID field was in timestamp order . ID would be unique (assuming an autonumber) while strictly speaking the timestamp might not be.

 

All the best

 

Keith

[5kyy8lu3]

Hi

 

It would be safer and faster if the ID field was in timestamp order . ID would be unique (assuming an autonumber) while strictly speaking the timestamp might not be.

 

All the best

 

Keith

so I guess I'd have to make ID auto increment backwards? is that even possible with just mysql?

 

i could use php to set the ID value so it decreases with each entry but that would require one extra query and a little more overhead. i'm guessing there's some mysql method of accomplishing this.

[kickstart]

Hi

 

It might be possible, but don't think it is necessary. Just so long that the ID field is in timestamp order (assuming you haven't invented a time machine and so want to add events as they occur in reverse order  ).

 

All the best

 

Keith

[5kyy8lu3]

ok i see what you mean, i didn't realize my example table was that way, my actual table has the ID and Timestamp columns in the same order