Hi,

I have a table to store the login history of clients. One of the
captured fields is the timestamp. I am trying to write a query to get
all but the last row in the login history for a user.

I thought something like this should work:

SELECT * FROM LoginHistory L
WHERE USER_ID like 'testuser (AT) company (DOT) com'
HAVING L.TIMESTAMP < MAX(L.TIMESTAMP);

This seems to select only the first row. Any idea why this is
happening and how to do it correctly?

Thanks

On 17-11-10 11:13, Mark Smith wrote:
SELECT * FROM LoginHistory L
WHERE USER_ID like 'testuser (AT) company (DOT) com'
HAVING L.TIMESTAMP< (
SELECT MAX(M.TIMESTAMP)
FROM LoginHistory M
WHERE M.USER_ID like 'testuser (AT) company (DOT) com');


--
Luuk

On Nov 17, 10:27*am, Luuk <L... (AT) invalid (DOT) lan> wrote:
Thanks.

Although is there a reason mysql can't / won't do this with just one
select?

Mark Smith <marksmith5555 (AT) jungle-monkey (DOT) com> wrote:
SELECT * FROM LoginHistory L
WHERE USER_ID like 'testuser (AT) company (DOT) com'
ORDER BY L.TIMESTAMP DESC
LIMIT 1, $REALLY_LARGE_NUMBER

See: http://dev.mysql.com/doc/refman/5.1/en/select.html
for the LIMIT clause

Better omit the LIMIT clause and simply drop the first
tuple from the result set.

Of course this query will have bad performance, especially
if there are many entries for a given user.

Last not least: USER_ID should be better a number than a
string and you should use the = operator instead of LIKE.


XL

On 17-11-10 13:21, Mark Smith wrote:
its not mysql that can't do it with one select
or can you show /me ?

--
Luuk

For consideration...

SELECT * FROM ints;
+---+

SELECT DISTINCT i1.* FROM ints i1 JOIN ints i2 ON i2.i > i1.i;
+---+