Just playing around a little with this problem, I think I found something
that works. Here it is,
CREATE TABLE `o_sessions` (
`isession` int(11) NOT NULL auto_increment,
`iuser` int(11) NOT NULL default '0',
`ssession` varchar(50) NOT NULL default '',
PRIMARY KEY (`isession`)
) TYPE=MyISAM;
INSERT INTO o_sessions VALUES("1","41","not wanted");
INSERT INTO o_sessions VALUES("2","41","wanted");
INSERT INTO o_sessions VALUES("3","42","not wanted");
INSERT INTO o_sessions VALUES("4","42","wanted");
INSERT INTO o_sessions VALUES("5","43","not wanted");
INSERT INTO o_sessions VALUES("6","43","wanted");
SELECT iuser, MAX(isession) as isession, MAX(ssession) as ssession from
o_sessions GROUP BY iuser;
+----------+--------------+
| iuser | isession | ssession
+----------+--------------+
| 41 | 2 | wanted
| 42 | 4 | wanted
| 43 | 6 | wanted
+----------+--------------+
Mike
mysql
----- Original Message -----
From: "Matthew Scarrow" <[EMAIL PROTECTED]>
To: "Robo" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; "denonymous"
<[EMAIL PROTECTED]>
Sent: Wednesday, July 17, 2002 10:53 AM
Subject: Re: still not a simple sql-question ! ...
> Add the sSession field and put in the not wanteds and wanteds you will see
that the 3 rows your query returns is the first ones in the list per iUser
that are the not wanteds. The query somehow needs to select the second ones.
This is where the problem comes in.
>
> ---------- Original Message ----------------------------------
> From: "denonymous" <[EMAIL PROTECTED]>
> Date: Wed, 17 Jul 2002 09:23:30 -0400
>
> >From: "Robo" <[EMAIL PROTECTED]>
> >
> >> Well, by now i have found a way to work around the problem,
> >> still the problem is not solved, you may want continue thinking about
it:
> >>
> >> start with this:
> >> iSession iUser sSession
> >> ---------- ---------- -----------------
> >> 1 41 no
> >> 2 41 wanted
> >> 3 42 no
> >> 4 42 wanted
> >> 5 43 no
> >> 6 43 wanted
> >>
> >> and end up with that:
> >> iSession iUser sSession
> >> ---------- ---------- -----------------
> >> 2 41 wanted
> >> 4 42 wanted
> >> 6 43 wanted
> >>
> >> Do it without a subselect and do it with one sql statement
> >
> >
> >I'm new to this thread, so bear with me if this has already been
suggested,
> >but I just did the following and it worked fine:
> >
> >mysql> CREATE TABLE `O_Sessions` (
> > `iSession` int(11) NOT NULL auto_increment,
> > `iUser` int(11) NOT NULL default '0',
> > PRIMARY KEY (`iSession`)
> >) TYPE=MyISAM;
> >
> >mysql> INSERT INTO O_Sessions (iSession,iUser) VALUES
> >(1,41),(2,41),(3,42),(4,42),(5,43),(6,43);
> >
> >mysql> SELECT * FROM O_Sessions ORDER BY iSession;
> >+----------+-------+
> >| iSession | iUser |
> >+----------+-------+
> >| 1 | 41 |
> >| 2 | 41 |
> >| 3 | 42 |
> >| 4 | 42 |
> >| 5 | 43 |
> >| 6 | 43 |
> >+----------+-------+
> >6 rows in set (0.01 sec)
> >
> >mysql> SELECT MAX(iSession), iUser FROM O_Sessions GROUP BY iUser;
> >+---------------+-------+
> >| MAX(iSession) | iUser |
> >+---------------+-------+
> >| 2 | 41 |
> >| 4 | 42 |
> >| 6 | 43 |
> >+---------------+-------+
> >3 rows in set (0.00 sec)
> >
> >
> >Does that last query help?
> >
> >
> >
> >--
> >Mike Johnson . : . : . AIM: denonymous
> >http://www.coldcircuit.net ' : ' : ' http://65.96.177.11
> >
> >"According to one of our readers, the new MacOS X contains another
> > Satanic holdover from the 'BSD Unix' OS mentioned above; to open up
> > certain locked files one has to run a program much like the DOS
> > prompt in Microsoft Windows and type in a secret code: 'chmod 666'."
> >
> >
> >
> >---------------------------------------------------------------------
> >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
---------------------------------------------------------------------
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
