Re: Simple SQL Question
Anybody? Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
[snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city Jay Blanchard wrote: [snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
[snip] What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city [/snip] Crud! Standing too close to the forest and forgot about a self join... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Good one. I don't know how I missed this either! Thanks! gerald_clark [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] What about select distinct a.region, a.city from mytable a , mytable b where a.region=b.region and a.city b.city Jay Blanchard wrote: [snip] Anybody? I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles There is no good way to get this in a single query (w/o subqueries). Having applied all sorts of query mangling you would have to be able to carry forward some sort of count or variable in order to draw out the ones where the foreign key was 1. Grouping by the city does not work either as that reduces any count to a one for that record. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple SQL Question
I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple SQL Question
Sorry, I forgot to mention I am using version 4.0.20a (no subqueries supported) Jeff Burgoon [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have a simple problem and I'm just wondering the BEST query to solve it. I want to return all the rows of a table whose foreign key value exists more than once in that table. IE... MyTable Region(foreign key)City EastBaltimore EastPhilly EastNewark MidwestCleveland SouthFort Lauderdale West Phoenix WestLos Angeles I want a query that returns only the rows where there are more than one of that particular Region in MyTable. The values returned would be EastBaltimore EastPhilly EastNewark WestPhoenix WestLos Angeles Here is what I'd like to do (but can't because the current stable build of MySQL doesn't support subqueries) SELECT MyTable.* FROM (SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2) as Duplicates, MyTable WHERE Duplicates.Region = MyTable.Region Here is what I'm actually doing: CREATE TEMPORARY TABLE Duplicates SELECT Region, Count(*) as cnt FROM MyTable GROUP BY Region HAVING cnt = 2; SELECT MyTable.* FROM MyTable, Duplicates WHERE MyTable.Region = Duplicates.Region; Can anybody tell me if there is a more efficient way of doing this query? Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
still not a simple sql-question ! ...
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 SELECT * FROM O_Sessions AS first, O_Sessions AS second WHERE first.iSession=MAX(second.iSession) AND first.iUser=second.iUser so far is the only solution, but does not work with mysql :-( rain outside, 14:45, three hours after breakfast and now heading for some new problems, robo :-) - 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
Re: still not a simple sql-question ! ...
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
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
Re: still not a simple sql-question ! ...
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
??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
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) - 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
Re: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
On 16 Jul 2002, at 21:39, Robo wrote: 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 :-( I'm not sure what you want, but the query could be SELECT iUser, MAX(iSession) FROM O_Sessions GROUP BY iUser; If that's not it, you'll need to explain more. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - 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
RE: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
Well if you want the latest and greatest iSession irrespective of the user use select max(iSession) from O_Sessions; If it is to be grouped by user, then select user, max(iSession) from O_Sessions group by user; This will give you the max iSession for a user. Regards Satish -Original Message- From: Robo [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 16, 2002 12:39 PM To: [EMAIL PROTECTED] Subject: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser 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) - 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
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
Re: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser
Have you tried something like this, SELECT iSession FROM O_Sessions GROUP BY iUser ORDER BY iSessions DESC; Mike - Original Message - From: Robo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 16, 2002 3:39 PM Subject: ??? Simple sql-question: SELECT iSession FROM O_Sessions GROUP BY iUser 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) - 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
not so simple sql-question?: ... O_Sessions AS first, O_Sessions AS second ...
SELECT * FROM O_Sessions AS first, O_Sessions AS second WHERE first.iSession=MAX(second.iSession) AND first.iUser=second.iUser yes, nice idea, but in mySQL, grouping functions like MAX are only allowed in SELECT ... FROM, not in the WERE part :-( But i will remember this way to work around the GROUP BY. What about speed ? System would only have to group 'second', in order to find the recordset, that has the greatest iSession... Thank's for your suggestion anyway, roland (poor little mysql user :-) ) ... My work around in mysql: i simply store data from the latest O_Sessions in additional fields of O_Users, thereby omitting any GROUP BY things :-) 3:22 not far from dawn, now heading directly for bed, roland two weeks from now, have a look at www.snoopme.com :-) - 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
Re: not so simple sql-question?: ... O_Sessions AS first, O_Sessions AS second ...
Hi. On Wed 2002-07-17 at 03:21:34 +0200, [EMAIL PROTECTED] wrote: SELECT * FROM O_Sessions AS first, O_Sessions AS second WHERE first.iSession=MAX(second.iSession) AND first.iUser=second.iUser yes, nice idea, but in mySQL, grouping functions like MAX are only allowed in SELECT ... FROM, not in the WERE part :-( What the above query seems to try to do is explained in the tutorial section of the manual: http://www.mysql.com/doc/e/x/example-Maximum-row.html and basically boils down to either using a MySQL-ism (the LIMIT in: ... ORDER BY second.iSession DESC LIMIT 1) or using two steps. Greetings, Benjamin. -- [EMAIL PROTECTED] - 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
Re: simple SQL question
[EMAIL PROTECTED] wrote: Hi, Sorry for the possible offtopic question I'm going to ask. I have got something similar (this is very simplicated situation of my problem but this is the core of my headache): CREATE TABLE cityname ( id BIGINT NOT NULL AUTO_INCREMENT, cname CHAR(50), INDEX id_index(id) ); CREATE TABLE firms ( id BIGINT NOT NULL AUTO_INCREMENT, fname CHAR(50), city0 BIGINT, city1 BIGINT, INDEX id_index(id), INDEX city0_index(city0), INDEX city1_index(city1) ); Now I want to dump data out from my database with textual names of cities. if I do: SELECT * FROM firms; it's not good since I have to resolve the city names in further queries which seems to a bit expensive (inmagine that each record has got 6 cities. note that I had to use maximum of 6 cities so I don't want to create another table for the relation, but please ask me to this - and tell me how and why is it better - if it's better solution). What can I do to have something similar result: A+B company Dallas London New systems Ltd New YorkParis My knowledge in this situation is not enough for talking about mysql. Please help me, and CC the letter for me as well, since I'm not on any mysql mailing list. Maybe my SQL knowledge is not too good as well to ask such a stupid question :) By the way, where can I ask questions like this especially for MySQL? Maybe I should read some documents can be found on the net ... Tell URLs. Or whatever. I would suggest not having 2 cities in your firm record, and making fname,city your key. select * from firms order by fname,city would give you. A+B company Dallas A+B company London New systems Ltd New York New systems Ltd Paris - 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
Re: simple SQL question
On Thu, Jan 25, 2001 at 12:52:11PM -0600, Gerald L. Clark wrote: I would suggest not having 2 cities in your firm record, and making fname,city your key. select * from firms order by fname,city would give you. A+B company Dallas A+B company London New systems Ltd New York New systems Ltd Paris Nice, but it has got some problems. If I correctly understand you, you suggest me to double records which have got multiple city entries. The problem is that firms table has got many fields even binary ones to hold picture data so it would be expensive to double them. And my other problem: indexing character types are more slower than just bigint values. By the way is there any ANSI SQL solution for it? I mean only in mySQL can't be implemented simply or this is a general SQL problem? PS: Maybe I should stop CC'ing this thread to the list, shouldn't I. - Gabor Lenart - 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
RE: simple SQL question
Hi, how about select fname, c1.cname, c2.cname, c3.cname from firms, cityname as c1, cityname as c2, cityname as c3 where first.city0 = c1.id and first.city1 = c2.id and first.city2 = c3.id; CC'ing the dialogue to the list lets us know that you have received the first reply, and what you made of it, allowing others, like me, to suggest other solutions. Regards Quentin -Original Message- From: Gbor Lnrt [mailto:[EMAIL PROTECTED]] Sent: Friday, 26 January 2001 09:20 To: Gerald L. Clark Cc: [EMAIL PROTECTED] Subject: Re: simple SQL question On Thu, Jan 25, 2001 at 12:52:11PM -0600, Gerald L. Clark wrote: I would suggest not having 2 cities in your firm record, and making fname,city your key. select * from firms order by fname,city would give you. A+B company Dallas A+B company London New systems Ltd New York New systems Ltd Paris Nice, but it has got some problems. If I correctly understand you, you suggest me to double records which have got multiple city entries. The problem is that firms table has got many fields even binary ones to hold picture data so it would be expensive to double them. And my other problem: indexing character types are more slower than just bigint values. By the way is there any ANSI SQL solution for it? I mean only in mySQL can't be implemented simply or this is a general SQL problem? PS: Maybe I should stop CC'ing this thread to the list, shouldn't I. - Gabor Lenart - 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 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - 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