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]
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]
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
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
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
