Salutations Robo, You are very close. Try: Select * from O_sessions order by iSession DESC limit 1,1
Or if you don't care how many records are returned: Select * from O_sessions order by iSession DESC and then just take the top record. No need to group by (that I can see) And, as always, NEVER use Select *...I'm using it as an example. :) =C= p.s. switch to home-cooked Italian. :) * * Cal Evans * The Virtual CIO * http://www.calevans.com * -----Original Message----- From: Robo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 4:39 PM To: [EMAIL PROTECTED] Subject: not so simple sql-question?: SELECT iSession FROM O_Sessions ORDER BY iSession DESC GROUP BY iUser thank's for your replies, but that did not help :-( this is the setting (also look below for example table): O_Sessions contains multilpe recordsets for iUser (let' say iUser=42). I want the last RECORDSET of this iUser=42, not the first. With GROUP BY, all the various recordsets with iUser=42 are thrown together, and mysql simply takes the first one (=>lowest iSession, as iSession is the increasing primary index.) But i need the last one of iUser=42 and the last one of iUser=42 and so on. SELECT iUser, MAX(iSession) FROM O_Sessions GROUP BY iUser; will not do, it will only select the greatest iSession for every iUser, not the latest RECORDSET. I need the recordset, as there is more data in O_Sessions like sSession, which holds the name for the session... SELECT iSession FROM O_Sessions GROUP BY iUser ORDER BY iUser DESC will also not do, as it only orderes the output (first iUser=42 then iUser=41, ...) and does nothing about the "pre-selection" during grouping. Maybe I could do with a sub-select, but mysql does not offer that ? And the querry would blow up anyway. I have demands like this from time to time, and have never found a satisfying solution. I fear, that sql does not support my wants and there is no way to tell the sql server which recordset to select when grouping the table. I think, grouping is implemented by simply advancing the recordsets an ignoring iUser=42 when one is found. That way, allways the first recordset is selected. But is there no way to tell the system to search the table downwards ? Something like this: SELECT iSession FROM O_Sessions ORDER BY iSession DESC GROUP BY iUser (not sql) well, i am currently making a halt in programming, hoping for you to find a solution :-) now entirely dark in Germany, but stomach still quite full with self-cooked chinese food. Roland :-) ------------------------------ O_Sessions: | iSession | iUser | sSession| | 1 | 41 | "not wanted" | | 2 | 41 | "wanted" | | 3 | 42 | "not wanted" | | 4 | 42 | "wanted" | | 5 | 43 | "not wanted" | | 6 | 43 | "wanted" | ------------------- --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
