Group By / Order BY
I've got an issue with group / order by.. here's what I'm wondering.. simple table.. date | rev -- 20070315 1 20070315 2 20070316 1 20070316 2 Query I'm running SELECT * FROM `table` GROUP BY `date` order by `rev` DESC I would think this would return 20070315 - 2 20070316 - 2 but it doesn't it returns 20070315 - 1 20070316 - 1 What am I missing? I'm trying to do a group by the date and return the higher rev.. thanks agian! Justin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By / Order BY
1) ORDER BY is executed after GROUP BY; 2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an aggregated value; Your query can be rewritten as: select date , max(rev) as max_rev from table group by date order by max_rev desc 2007/3/17, Justin [EMAIL PROTECTED]: I've got an issue with group / order by.. here's what I'm wondering.. simple table.. date | rev -- 20070315 1 20070315 2 20070316 1 20070316 2 Query I'm running SELECT * FROM `table` GROUP BY `date` order by `rev` DESC I would think this would return 20070315 - 2 20070316 - 2 but it doesn't it returns 20070315 - 1 20070316 - 1 What am I missing? I'm trying to do a group by the date and return the higher rev.. thanks agian! Justin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olexandr Melnyk, http://omelnyk.net/
Re: Group By / Order BY
hmm.. that worked.. but I guess I forgot to mention.. I don't need the value of the rev.. I wanted to return that row.. like.. let me put some more info date revtext desc - 20070315 1this is version 1 This was the first one today 20070315 2this is version 2 This was the first one today, but edited 20070316 1this is version 1 This was the first one today 20070316 2this is version 2 This was the first one today but agian we needed to update. I want to pull distinct rows (there could be as many as 30 rev's per date) and the data on that row. so.. I'd like to get the following 20070315 2this is version 2 This was the first one today, but edited 20070316 2this is version 2 This was the first one today but agian we needed to update. does that make sense? - Original Message - From: Olexandr Melnyk [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, March 17, 2007 3:56 PM Subject: Re: Group By / Order BY 1) ORDER BY is executed after GROUP BY; 2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an aggregated value; Your query can be rewritten as: select date , max(rev) as max_rev from table group by date order by max_rev desc 2007/3/17, Justin [EMAIL PROTECTED]: I've got an issue with group / order by.. here's what I'm wondering.. simple table.. date | rev -- 20070315 1 20070315 2 20070316 1 20070316 2 Query I'm running SELECT * FROM `table` GROUP BY `date` order by `rev` DESC I would think this would return 20070315 - 2 20070316 - 2 but it doesn't it returns 20070315 - 1 20070316 - 1 What am I missing? I'm trying to do a group by the date and return the higher rev.. thanks agian! Justin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olexandr Melnyk, http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group By / Order BY
I want to pull distinct rows (there could be as many as 30 rev's per date) and the data on that row. so.. I'd like to get the following GROUP BY groups by aggregate value, not by row. Use a WHERE clause to return a row corresponding to the value of a column. PB Justin wrote: hmm.. that worked.. but I guess I forgot to mention.. I don't need the value of the rev.. I wanted to return that row.. like.. let me put some more info date revtext desc - 20070315 1this is version 1 This was the first one today 20070315 2this is version 2 This was the first one today, but edited 20070316 1this is version 1 This was the first one today 20070316 2this is version 2 This was the first one today but agian we needed to update. I want to pull distinct rows (there could be as many as 30 rev's per date) and the data on that row. so.. I'd like to get the following 20070315 2this is version 2 This was the first one today, but edited 20070316 2this is version 2 This was the first one today but agian we needed to update. does that make sense? - Original Message - From: Olexandr Melnyk [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Saturday, March 17, 2007 3:56 PM Subject: Re: Group By / Order BY 1) ORDER BY is executed after GROUP BY; 2) In ORDER BY don't use columns that aren't in GROUP BY, unless it's an aggregated value; Your query can be rewritten as: select date , max(rev) as max_rev from table group by date order by max_rev desc 2007/3/17, Justin [EMAIL PROTECTED]: I've got an issue with group / order by.. here's what I'm wondering.. simple table.. date | rev -- 20070315 1 20070315 2 20070316 1 20070316 2 Query I'm running SELECT * FROM `table` GROUP BY `date` order by `rev` DESC I would think this would return 20070315 - 2 20070316 - 2 but it doesn't it returns 20070315 - 1 20070316 - 1 What am I missing? I'm trying to do a group by the date and return the higher rev.. thanks agian! Justin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] - Olexandr Melnyk, http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY ORDER BY
I'm trying to SELECT the most recent record in a table for each person record in another table. Here's what I have so far: SELECT history.*, persons.person_short_name, persons.person_long_name FROM history, persons WHERE persons.id = history.person_id AND persons.status = 1 GROUP BY history.person_id ORDER BY history.time_sec DESC The good thing: It retrieves DISTINCT persons (no duplicates). The problem: The history rows are not the most recent for each person. What I would need, theoretically, is for the ORDER BY clause to go before GROUP BY, but MYSQL doesn't like that it seems. Any ideas? Thanks. ...René --- René Fournier www.renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY ORDER BY
GROUP BY returns grouped columns and aggregate functions, not rows. You are grouping on history.person_id, so it makes no sense to select any column that does not have a unique value for each history.person_id. Indeed, other systems wouldn't even allow selecting non-grouped columns http://dev.mysql.com/doc/mysql/en/group-by-hidden-fields.html. Hence your problem. You want to select the rows where the history.time_sec is the most recent (MAX) per group. That's essentially a 2-step process: first find the max history.time_sec for each group, then select the rows which match. You can either save the result of the first step in a temporary table to use in the second step, or, if you have at least 4.1, you can use a subquery to write it as one statement. The manual has examples, as well as a 3rd, inefficient, 1-step method http://dev.mysql.com/doc/mysql/en/example-maximum-column-group-row.html. Michael René Fournier wrote: I'm trying to SELECT the most recent record in a table for each person record in another table. Here's what I have so far: SELECT history.*, persons.person_short_name, persons.person_long_name FROM history, persons WHERE persons.id = history.person_id AND persons.status = 1 GROUP BY history.person_id ORDER BY history.time_sec DESC The good thing: It retrieves DISTINCT persons (no duplicates). The problem: The history rows are not the most recent for each person. What I would need, theoretically, is for the ORDER BY clause to go before GROUP BY, but MYSQL doesn't like that it seems. Any ideas? Thanks. ...René --- René Fournier www.renefournier.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY, ORDER BY clauses
Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Asad Habib [EMAIL PROTECTED] wrote on 03/30/2005 10:53:38 AM: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad I hope you don't expect your query to actually work. The SELECT clause is the only place where you can use the * wildcard to mean all columns. If you wanted to frame a sample query but leave out information, I have been using an ellipsis (three dots together) to indicate the missing piece(s) like this: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... However, I generally do not leave out EVERYTHING in a query, like the one above. I usually only leave out the parts that aren't important to the information I am trying to convey. If I want to indicate that there was something specific the user needs to replace, I put that inside of angle brackets like this SELECT a list of columns from your table , a list of aggregate functions on columns from your table FROM a table name GROUP BY all of the columns in your SELECT clause that are not part of an aggregate function These are just my conventions. Use them only if you like them. I am not nor will I ever become the style police for this list. I just thought you could use a little help in creating better sample queries :-) You asked if you can use GROUP BY and ORDER BY in the same query. Absolutely!! I do it frequently. You also asked if GROUP BY works on different column types. Absolutely!! You can group on any type of field or combination of datatypes supported by MySQL (with the exception of TEXT and BLOB fields as they usually contain more data than is practical to use to form aggregates. I would recommend that you do not use a BLOB or TEXT field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you should manually specify what portion of the field to use) May I suggest some reading? http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html http://dev.mysql.com/doc/mysql/en/blob.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: GROUP BY, ORDER BY clauses
Sorry for the confusion. In this case I am using the * to denote a field name instead of the wild card character. - Asad On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote: Asad Habib [EMAIL PROTECTED] wrote on 03/30/2005 10:53:38 AM: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad I hope you don't expect your query to actually work. The SELECT clause is the only place where you can use the * wildcard to mean all columns. If you wanted to frame a sample query but leave out information, I have been using an ellipsis (three dots together) to indicate the missing piece(s) like this: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... However, I generally do not leave out EVERYTHING in a query, like the one above. I usually only leave out the parts that aren't important to the information I am trying to convey. If I want to indicate that there was something specific the user needs to replace, I put that inside of angle brackets like this SELECT a list of columns from your table , a list of aggregate functions on columns from your table FROM a table name GROUP BY all of the columns in your SELECT clause that are not part of an aggregate function These are just my conventions. Use them only if you like them. I am not nor will I ever become the style police for this list. I just thought you could use a little help in creating better sample queries :-) You asked if you can use GROUP BY and ORDER BY in the same query. Absolutely!! I do it frequently. You also asked if GROUP BY works on different column types. Absolutely!! You can group on any type of field or combination of datatypes supported by MySQL (with the exception of TEXT and BLOB fields as they usually contain more data than is practical to use to form aggregates. I would recommend that you do not use a BLOB or TEXT field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you should manually specify what portion of the field to use) May I suggest some reading? http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html http://dev.mysql.com/doc/mysql/en/blob.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Just keep in mind that the ORDER BY will require MySQL to take the full resultset and reorder it in a temporary table. MySQL has extended the GROUP BY clause as of version 3.23.34 so that you can also specify ASC and DESC after columns named in the clause. On Wed, 30 Mar 2005 10:53:38 -0500 (EST), Asad Habib [EMAIL PROTECTED] wrote: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Group By Order By problem
Hi all, I am trying to get a bunch of results, group them by category, and then order each group of categories. My query is thus: SELECT l.CatalogNumber, l.MP3Name, l.PDFLink, l.PDFName, l.Title, p.PublisherName, c.ComposerLname, a.ArrangerLname, l.Price, l.Description, o.Alias FROM listings l, publishers p, composers c, arrangers a, categories o WHERE (a.ArrangerLname like '%$Criteria%' or p.PublisherName like '%$Criteria%' or c.ComposerLname like '%$Criteria%' or l.Title like '%$Criteria%' or l.CatalogNumber like '%$Criteria%' or l.Price like '%$Criteria%' or l.Description like '%$Criteria%') AND l.PublisherID=p.PublisherID and l.ComposerID=c.ComposerID and l.ArrangerID=a.ArrangerID and l.CategoryID=o.CategoryID GROUP BY o.Alias ASC ORDER BY o.Alias, c.ComposerLname ASC; This only displays 1 row in each category, so clearly I have an error in my Group By and/or Order By clause(s). I am sure my error is fairly basic, but I don't have enough experience with MySQL to figure it out. Does anyone have any insight into the problem? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by order by rand() problem
Alle 21:57, giovedì 1 aprile 2004, Michael Stassen ha scritto: You could probably accomplish this with a variant of the MAX-CONCAT trick http://www.mysql.com/doc/en/example-Maximum-column-group-row.html. Something like: SELECT user_id, SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title FROM banners GROUP BY user_id; Thank you so much. This solution works correctly. -- Alessandro 'Asterix' Astarita [EMAIL PROTECTED] CapriOnLine S.r.l. http://www.caprionline.com/ Unix IS user friendly. It's just selective about who its friend are -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: group by order by rand() problem
Right. You're grouping by user_id and throwing in title, and you're hoping to influence which of the titles is chosen to go with user_id, but as title is neither part of your group nor part of an aggregate function, its value is undefined. See the manual for an explanation http://www.mysql.com/doc/en/GROUP-BY-hidden-fields.html. You could probably accomplish this with a variant of the MAX-CONCAT trick http://www.mysql.com/doc/en/example-Maximum-column-group-row.html. Something like: SELECT user_id, SUBSTRING(MAX(CONCAT(TRUNCATE(RAND(),4),title)),7) AS Title FROM banners GROUP BY user_id; Michael m.pheasant wrote: Order by is working after the group stage. You would need an aggregate function which chooses a random row. Some other SQL implementations would not let you select a column that is not also grouped (eg title) or in an aggregate function as in your select ... group by ... example. m -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] I have this table: mysql select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
group by order by rand() problem
I have this table: mysql select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by order by rand() problem
Try seeding your rand. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 12:57 PM To: [EMAIL PROTECTED] Subject: group by order by rand() problem I have this table: mysql select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by order by rand() problem
Try seeding your rand. Tried. It doesn't work. The select shows always the same records but in different order: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 3 | 2 | third banner | | 1 | 1 | first banner | ++-+---+ etc... Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: group by order by rand() problem
Order by is working after the group stage. You would need an aggregate function which chooses a random row. Some other SQL implementations would not let you select a column that is not also grouped (eg title) or in an aggregate function as in your select ... group by ... example. m -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Thursday, April 01, 2004 7:16 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: group by order by rand() problem Try seeding your rand. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 12:57 PM To: [EMAIL PROTECTED] Subject: group by order by rand() problem I have this table: mysql select * from banners; ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 2 | 1 | second banner | | 3 | 2 | third banner | | 4 | 2 | forth banner | | 5 | 2 | fifth banner | ++-+---+ I would like to show a random banner for each user, something like this: first call ++-+---+ | id | user_id | title | ++-+---+ | 1 | 1 | first banner | | 3 | 2 | third banner | ++-+---+ second call ++-+---+ | id | user_id | title | ++-+---+ | 2 | 1 | second banner | | 4 | 2 | forth banner | ++-+---+ etc... I have tried with following query but the banner doesn't change while multiple calls: SELECT * FROM banners GROUP BY user_id ORDER BY RAND(); Can anyone help me? Thanks in advance, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY/ORDER BY Problem
The SQL specification does allow aggregates in the ORDER BY. Does mySQL have any plans to add such functionality (or at least add it to its list of things it doesn't do)? The problem with the solution of ordering by an alias is that I may not necessarily want the thing I'm ordering by to be in the result set. In the example below, I may just want to select the breed, ordered by minimum age, without showing the min. age. Is there a good way in mySQL to make this work? Thanks. Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY/ORDER BY Problem
At 5:52 -0700 10/3/03, Ed Smith wrote: Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function I don't believe that aggregate functions are legal in an ORDER BY clause. The solution, as you've found, is to select the value you want to order by, alias it, and refer to the alias in the ORDER BY clause. but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY/ORDER BY Problem
Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY ORDER BY
Hi I wonder if someone could help with what I assume is a simple query using GROUP and/or ORDER statements (something I struggle to get to grips with). I am trying to get a list of users who have posted to a forum by number of posts descending, but am unable to find the right statement to do it. At the moment I have: select count(*), user_id FROM messages WHERE forum_id = 294 GROUP BY user_id . which gets me what I want, but in a random user order. I have looked through the MySQL documentation, but have been unable to hit on the combination of functions needed to get what I need (which is effectively 'ORDER BY count(*) DESC'). Can anybody help? Many thanks Gary
Re: GROUP BY ORDER BY
try this: order by 1 desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY ORDER BY
Gary Broughton [EMAIL PROTECTED] wrote: I wonder if someone could help with what I assume is a simple query using GROUP and/or ORDER statements (something I struggle to get to grips with). I am trying to get a list of users who have posted to a forum by number of posts descending, but am unable to find the right statement to do it. At the moment I have: select count(*), user_id FROM messages WHERE forum_id = 294 GROUP BY user_id . which gets me what I want, but in a random user order. I have looked through the MySQL documentation, but have been unable to hit on the combination of functions needed to get what I need (which is effectively 'ORDER BY count(*) DESC'). SELECT COUNT(*) AS cnt, user_id FROM messages WHERE forum_id = 294 GROUP BY user_id ORDER BY cnt DESC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: GROUP BY ORDER BY
Hi Many thanks to one and all for your time and assistance with my question. I used the 'AS cnt' method and it works brilliantly. Simple isn't it? :-) Cheers Gary -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED] Sent: 24 July 2003 14:01 To: [EMAIL PROTECTED] Subject: Re: GROUP BY ORDER BY Gary Broughton [EMAIL PROTECTED] wrote: I wonder if someone could help with what I assume is a simple query using GROUP and/or ORDER statements (something I struggle to get to grips with). I am trying to get a list of users who have posted to a forum by number of posts descending, but am unable to find the right statement to do it. At the moment I have: select count(*), user_id FROM messages WHERE forum_id = 294 GROUP BY user_id . which gets me what I want, but in a random user order. I have looked through the MySQL documentation, but have been unable to hit on the combination of functions needed to get what I need (which is effectively 'ORDER BY count(*) DESC'). SELECT COUNT(*) AS cnt, user_id FROM messages WHERE forum_id = 294 GROUP BY user_id ORDER BY cnt DESC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New Group By, order by question
All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New Group By, order by question
Hi Martin, I understand the ABS() function is used for this. Best regards Nils Valentin Tokyo/Japan 2003 7 24 23:42Martin Moss : All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New Group By, order by question
select order by abs(field) desc; Hope this helps, Joseph Bueno Martin Moss wrote: All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
So simple, yet wonderful:-) Re: New Group By, order by question
Thanks to everyone who Helped, Regards Marty - Original Message - From: Joseph Bueno [EMAIL PROTECTED] To: Martin Moss [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 24, 2003 3:56 PM Subject: Re: New Group By, order by question select order by abs(field) desc; Hope this helps, Joseph Bueno Martin Moss wrote: All, I have a question about grouping numbers. Lets say I have 10 records each containing a numeric value:- 1 2 3 5 10 -1 -2 -3 -4 -5 What I wish to do is to select the records from the database but group them like this :- e.g. by the highest value (ASC or DESC) regarldess of whether the value is positive or negative? 10 5 -5 -4 3 -3 2 -2 1 -1 Can I do this in one query, or do I need to make two queries Regards Marty -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sql query(group by + order by)
Hi, I got a table named patient_treatment_history Below is some of the records inside the table. patient_id,treatment_date,charges 1014,2002-01-28,20 1001,2002-02-02,100 1026,2002-04-08,74 1001,2002-04-15,85 1014,2002-05-05,50 1030,2002-05-16,125 1030,2002-06-18,180 1001,2002-06-25,125 I try to retrieve the last visit date for each patient using this query: select patient_id,treatment_date from patient_treatment_history group by(patient_id) order by treatment_date desc. But the result is not what i expected. Can anybody assist me on this? Thanks. - 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: sql query(group by + order by)
do a select patient_id,treatment_date from patient_treatment_history order by treatment_date desc limit 1; I am not sure but max(treatmen_date) might work too as such: select patient_id, max(treatment_date) from patient_treatment_history group by treatment_date; In your original query the group by clause is causing undesired results. - Original Message - From: lorenzo.kh [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, June 10, 2002 6:53 PM Subject: sql query(group by + order by) Hi, I got a table named patient_treatment_history Below is some of the records inside the table. patient_id,treatment_date,charges 1014,2002-01-28,20 1001,2002-02-02,100 1026,2002-04-08,74 1001,2002-04-15,85 1014,2002-05-05,50 1030,2002-05-16,125 1030,2002-06-18,180 1001,2002-06-25,125 I try to retrieve the last visit date for each patient using this query: select patient_id,treatment_date from patient_treatment_history group by(patient_id) order by treatment_date desc. But the result is not what i expected. Can anybody assist me on this? Thanks. - 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
MySQL 4.0.1 bug ... no returned results using GROUP and ORDER
Description: When issuing a command containing both a GROUP BY and an ORDER BY clause, the server is returning no results. This issue has arisen since upgrading from 3.23.47 to 4.0.1 How-To-Repeat: Table Creation: CREATE TABLE statarchive (uid INT unsigned NOT NULL AUTO_INCREMENT, referal VARCHAR(15), date DATE, uniques int unsigned, raws int unsigned, site VARCHAR(10), signups INT unsigned, payout decimal(8,2), bsff mediumint unsigned, PRIMARY KEY(uid)); example select code: SELECT date, sum(uniques) as cnt1, sum(signups) as cnt2, sum(payout) as cnt3 FROM statarchive WHERE date BETWEEN '2002-01-29' AND '2002-02-05' GROUP BY date ORDER BY date DESC; Will return 0 results constantly (with any use of ORDER BY, ASC or DESC). Without the ORDER BY clause the server returns the 7 days worth of stats appropriately calculated Submitter-Id: submitter ID Originator: Organization: MySQL support: none Synopsis: GROUP BY w/ and ORDER BY clause returns no results Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.47 (Source distribution) Server: /bin/mysqladmin Ver 8.23 Distrib 3.23.47, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 3 days 1 hour 7 min 1 sec Threads: 132 Questions: 4144248 Slow queries: 0 Opens: 1677 Flush tables: 1 Open tables: 16 Queries per second avg: 15.744 Environment: System: Linux clarke 2.2.20 #2 SMP Tue Jan 29 13:22:41 CST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Jan 29 03:30 /lib/libc.so.6 - libc-2.1.2.so -rwxr-xr-x 1 root root 1008844 Sep 9 1999 /lib/libc-2.1.2.so -rw-r--r-- 1 root root 20019674 Sep 15 1999 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Sep 15 1999 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql Perl: This is perl, version 5.005_03 built for i386-linux - 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
group by, order by, temporary table
Hi there, I'm back with yet some questions about group by/order by in mysql.. My first question is how group by is beeing done if I have have columns in the select query that is not in the group by statement. Dring many test with 3.23-42 (Myisam tables) I found out that the content of the columns that is not in the was showing values for the first physical line in the database (withing the group criteria ofcourse) and here is my question... CAN I relly on the result that I have detected, if I can, is this table type dependent? will it change if I move from myIsam to innodb or some other table type? might this change to never versions of mysql? is this different depending on the operation system that mysql is running on? I have found out that by using this fact I can get some of my queries to work even better (I do not have to do that much logic programming in the program that does the query...) My second question is regarding temporary tables and order by , the answer to this affectsmy first question :-) if I make a query in mysql like this.. create temporary table test select * from real_table order by first_key, second_key; Will the rows be stored (physically) in the order that is stated in the order by statement? when the resulting temporary table is very small (lets say less then 100 rows) is it possiblefor mysql to cache the table completely in memory and when it is larger to savce it to disk(these temporary tables will only exist a very short time (create temp.., select from temp..., drop temp.) I really do not need them to be stored on the disc, unless they are to big and the result cannot be kept in memory.. Im asking this since I have a small problem that I have managed to solve with the above asked questions... I create a temporary table with the result from a join between 2 tables (head,lines) but since the resultcontaines to many lines (I only need 1 of them) and since I cannot use simple where to just select the line (trust me I cannot I have tried, perheps with) I use group by on the resulting temporary table. and finally drops the table... what I get is that I can select from which rows the data NOT in the group by statement will used as the resulting data.. one Oould say that I have by this created order by zzz group by yyy which in mysql does not exist (or in ansi sql either I guess) my other option would have been to do the join as usual, but not put the result in a temporary table, but retrieve it myself.. I would then programaticly have to create my group by and select the rows I want to..' with 3.23-42 on windows 2000 this actually works.. and it works very well One question to the mysql team (or any other mysql developer) since you allow columns in the query that does not exist in the group by, will you let us somehow decide which rows (from the ones grouped) the values will be taken from (like order by zzz group by yyy ) ? Well that was it for now from me :-) Merry X-mas and a Happy New Year! to all of you! /Christian - 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
