sql GROUP BY clause

[Camel]

consider:

Code Select Expand

SELECT lastlogin, time
    FROM whatever
    GROUP BY ip;

when the query returns, each results will be the first row containing each unique ip. is there a way to order the results so that it's the last row containing each unique ip? perhaps "SELECT FROM (SELECT FROM ... ORDER BY time DESC) GROUP BY ip" could work, but is there a simpler way?

[Grok]

consider:

Code Select Expand

SELECT lastlogin, time
    FROM whatever
    GROUP BY ip;

when the query returns, each results will be the first row containing each unique ip. is there a way to order the results so that it's the last row containing each unique ip? perhaps "SELECT FROM (SELECT FROM ... ORDER BY time DESC) GROUP BY ip" could work, but is there a simpler way?

Yes.  You're looking for a self join.  I'll just give you a hint for now.  Use inner join and the max(time) function and put IP in the where clause.  Try that and if you aren't having fun I'll show you how.

Oh and I'm hoping your 'time' is really a datetime.  But I suspect lastlogin is the date, and time is the time-of-day.  That's going to make it more difficult but you can use the convert() function to solve that in your answer.

[Camel]

time is a timestamp. i find the 'date' and 'time' types rather agrivating
lastlogin is actually a string (basicly i'm trying to display the last login name that ip used)
anyways, i'll try that

[Camel]

ok i give up o_O

[Grok]

To display the last login name by IP.

1)  If the table contains only the last login for an IP:

SELECT ip, lastlogin, time FROM T1 ORDER BY time

2)  If the table contains all logins + times for an IP:

select a.ip, a.lastlogin, a.time
from t1 a inner join (select ip, max(time) as time from t1 group by ip) b
on a.ip = b.ip
where a.time = b.time

Ugly but it works.  I'm sure there's a simpler way to do it, but that's all the comes to mind at the moment.

P.S.  Your time is not a timestamp, it's a datetime.  The name is important because timestamp is an sequence tracking mechanism that you should not use normally.  A datetime is what you want.

[Camel]

grok, the whole point of asking was because mysql will *not* let you select from a select. unless i'm horribly mistaken, what you posted is near useless to me.


[edit] me fail english? that's unpossible!

[herzog_zwei]

MySQL has had (limited) subselects for about a year or so.

[Camel]

care to explain? i've had zero success

[herzog_zwei]

I'm just going by what the manual for MySQL says.  I haven't had the need to use MySQL for some time but I check it from time to time to see how it's matured.

Support for subqueries and derived tables

       * Subqueries are now supported. Here is an example:

SELECT * FROM t1 WHERE t1.a=(SELECT t2.b FROM t2);

SELECT * FROM t1 WHERE (1,2,3) IN (SELECT a,b,c FROM t2);

       * Derived tables (unnamed views) are now supported. Basically, a derived table is a subquery in the FROM clause of a SELECT statement. Here is an example:

SELECT t1.a FROM t1, (SELECT * FROM t2) t3 WHERE t1.a=t3.a;

[Grok]

MySQL has had (limited) subselects for about a year or so.

omg.  how does anyone get any real work done with it?  why are these fools going on and on about MySQL if it's so handicapped?

even MS-Access can select from select, and i wouldn't think of using that for a distributed production database for more than 10 users.

[Camel]

omg.  how does anyone get any real work done with it?  why are these fools going on and on about MySQL if it's so handicapped?

even MS-Access can select from select, and i wouldn't think of using that for a distributed production database for more than 10 users.

it's pure php that doesn't overlap with *anything* else. i use ms access (via odbc) as well, but not for this specific case.