simple question ? SELECT iSession FROM O_Sessions GROUP BY iUser

[Guest_imported]

I want the latest (highest) iSession to be selected:

SELECT iSession FROM O_Sessions GROUP BY iUser

Because of GROUP BY, allways the first(!) recordset for iUser is selected.
But i want the last recordset to be selected :-(

How can this be done ?
(mySQL)
(iSession ist the primary key => latest=highest. filling the primary key from 4294967295 down to 0
does not work)

greetings from germany (getting rather dark),
roland (Bollmann)

 

[carp]

Try:

SELECT iSession FROM O_Sessions GROUP BY iUser
ORDER BY iSession DESC

 

[ThomVF]

If sessions are numerically sequential, and ascending, why not try:

select max(iSession)
from O_Sessions
group by iUser

If they are descending, use MIN instead of MAX
??

 

[robosoft]

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 iSession DESC
will also not do, as it only orderes the output 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" |
-------------------

 

[ThomVF]

The example I gave you works correctly in ORACLE.
I do not have MySql available now so I am afraid I cannot help.

I suggest you post this to the MySQl forum....Good Luck
Tom

 

[carp]

Okay, I think I see what you are getting at.
Actually, SQL WILL do what you want, but perhaps mySQL doesn't support the requisite SQL.
Try this:

select * from osession
where isession in
(select max(isession)
from osession
group by iuser);


ISESSION IUSER SSESSION
---------- ---------- -----------------
2 41 wanted
4 42 wanted
6 43 wanted

However, this IS using a subquery, which you indicate mySQL cannot handle. In which case, it is mySQL that is failing you, not SQL itself.

 

[angiole]

Güten Tag (That's good morning, not my name for all the tourists!)

What is this 'recordset' you refer to.
In other words, do you want to return the iUser once in the report line, showing the max iSession, or all the records that satisfy a summary?

AA 8~)



AA 8~)

 

[tlbroadbent]

I would use the following query if MySQL supports sub-queries.

Select o.* from osession As o
Where isession in
(Select max(isession)
From osession
Where iuser=o.iuser);

Another option follows. Again, I don't know if MySQL supports this.

Select o.*
From osession As o
Join (Select iuser, msession=max(isession)
From osession) As q
On o.iuser=q.iuser

If these SQL solutions don't work, I also recommend asking your question in forum436 - MySQL. Hopefully, other MySQL users will have a solution. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.