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

Reply via email to