Hello,
I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;"
I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ?
Thanks for your help,
Remi
Dear remi,
your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too.
If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web.
On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you.
lefteris
On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel rdaniel@inbox.fr wrote:
Hello,
I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;"
I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ?
Thanks for your help,
Remi
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Thanks for your reply,
My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function")
I will continue to search a solution,
Regards,
2013/6/28 Lefteris lsidir@gmail.com
Dear remi,
your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too.
If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web.
On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you.
lefteris
On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel rdaniel@inbox.fr wrote:
Hello,
I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;"
I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ?
Thanks for your help,
Remi
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Remi,
I guess the MonetDB error message is clear, isn't it?
With all honesty, I would not know what the expected result of grouping without aggregation is, i.e., the value of which (single) tuple is supposed to appear as result for a group of (multiple) tuples?
Having said that, without knowing the intentions of your query, we cannot give you any further advice. Sorry.
While MySQL and other DBMSs might have chosen to implement proprietary functionality (and syntax) that diverges from or goes beyond the SQL standard, MonetDB (tries to) stick to the standard.
Best, Stefan
----- Original Message -----
Thanks for your reply,
My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function")
I will continue to search a solution,
Regards,
2013/6/28 Lefteris lsidir@gmail.com
Dear remi,
your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too.
If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web.
On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you.
lefteris
On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel rdaniel@inbox.fr wrote:
Hello,
I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;"
I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ?
Thanks for your help,
Remi
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
And to be more specific on this issue, what MySQL does is it gives a random representative value of each of the fields 1 and 2 that belong to the group of field 3. From what I know that functionality is not part of the SQL standard.
If you know for example that field1 will always have the same value inside a group of field 3 then you can just add field1 to the groupby. If the values of field1 change inside a group of field3 then again I advice to put field 1 in the group by statement too because then you will get all subgroups.
It all depends what you want to do, what is the semantics of your query, thats why i suggested that it is not MonetDB specific, but SQL specific questions.
On Fri, Jun 28, 2013 at 6:00 PM, Stefan Manegold Stefan.Manegold@cwi.nl wrote:
Hi Remi,
I guess the MonetDB error message is clear, isn't it?
With all honesty, I would not know what the expected result of grouping without aggregation is, i.e., the value of which (single) tuple is supposed to appear as result for a group of (multiple) tuples?
Having said that, without knowing the intentions of your query, we cannot give you any further advice. Sorry.
While MySQL and other DBMSs might have chosen to implement proprietary functionality (and syntax) that diverges from or goes beyond the SQL standard, MonetDB (tries to) stick to the standard.
Best, Stefan
----- Original Message -----
Thanks for your reply,
My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function")
I will continue to search a solution,
Regards,
2013/6/28 Lefteris lsidir@gmail.com
Dear remi,
your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too.
If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web.
On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you.
lefteris
On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel rdaniel@inbox.fr wrote:
Hello,
I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;"
I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ?
Thanks for your help,
Remi
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
--
Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- | Stefan.Manegold@CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) |
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Indeed, the standard requires the result to be passed on towards an aggregate. It shows that in the fringes every DBMS may divert/violate from the standard. regards, Martin
On 6/28/13 5:44 PM, Rémi Daniel wrote:
Thanks for your reply,
My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function")
I will continue to search a solution,
Regards,
2013/6/28 Lefteris <lsidir@gmail.com mailto:lsidir@gmail.com>
Dear remi, your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too. If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web. On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you. lefteris On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel <rdaniel@inbox.fr <mailto:rdaniel@inbox.fr>> wrote: Hello, I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution : "SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;" I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases. Does anyone have any idea on how I can do that ? Thanks for your help, Remi -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel@inbox.fr <mailto:rdaniel@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list--
Rémi Daniel Développeur Web rdaniel@inbox.fr mailto:rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Stefan and Martin,
Thanks all for your feedback. Thats true, my query isn't very "regular"... I will search on sql forums.
I know at once that this isn't the place for my question, sorry for that.
But, just in case someone has an idea :
In fact, I have a table with 3 fields : email, address and update_date (update date of email address, so, there are several addresses for each email) What I want to do it's to get the last address of each email based on the date.
Something like "Select email, first(address) group by email;"
Regards,
Remi
2013/6/28 Martin Kersten martin@monetdb.org
Indeed, the standard requires the result to be passed on towards an aggregate. It shows that in the fringes every DBMS may divert/violate from the standard. regards, Martin
On 6/28/13 5:44 PM, Rémi Daniel wrote:
Thanks for your reply,
My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function")
I will continue to search a solution,
Regards,
2013/6/28 Lefteris <lsidir@gmail.com mailto:lsidir@gmail.com>
Dear remi, your question is a general SQL question and not a MonetDB specific.MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too.
If you have questions on how to formulate correct queries in SQL Iwould advice you to use one of the many SQL references available in the web.
On the other hand, if you ran a correct SQL query against monetdb andyou got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you.
lefteris On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel <rdaniel@inbox.fr<mailto:rdaniel@inbox.fr>> wrote:
Hello, I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request,and i don't find any solution :
"SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;" I want to do a GROUP BY of a field3 which is not in the SELECT toget a limit of request results by this field3, like the "first" function of some databases.
Does anyone have any idea on how I can do that ? Thanks for your help, Remi -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel@inbox.fr <mailto:rdaniel@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 ______________________________**_________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org**> http://mail.monetdb.org/**mailman/listinfo/users-list<http://mail.monetdb.org/mailman/listinfo/users-list> ______________________________**_________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org**> http://mail.monetdb.org/**mailman/listinfo/users-list<http://mail.monetdb.org/mailman/listinfo/users-list>--
Rémi Daniel Développeur Web rdaniel@inbox.fr mailto:rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list
On Fri, Jun 28, 2013 at 06:59:33PM +0200, Rémi Daniel wrote:
Hi Stefan and Martin,
Thanks all for your feedback. Thats true, my query isn't very "regular"... I will search on sql forums.
I know at once that this isn't the place for my question, sorry for that.
But, just in case someone has an idea :
In fact, I have a table with 3 fields : email, address and update_date ( update date of email address, so, there are several addresses for each email) What I want to do it's to get the last address of each email based on the date.
Something like "Select email, first(address) group by email;"
Something like
select email, address from ... a, (select email, max(update_date) from ... group by email) as b where a.email = m.email and a.update_date = m.update_date
This should work as long as the email/update_data combination is unique.
Niels
Regards,
Remi
2013/6/28 Martin Kersten martin@monetdb.org
Indeed, the standard requires the result to be passed on towards an aggregate. It shows that in the fringes every DBMS may divert/violate from the standard. regards, Martin On 6/28/13 5:44 PM, Rémi Daniel wrote: Thanks for your reply, My query works on Mysql but not on MonetDB, thats why I contact this maillist, to know if a specific function exist. (Sql functions aren't all the sames on all DBMS). (error message of my query : "cannot use non GROUP BY column 'field3' in query results without an aggregate function") I will continue to search a solution, Regards, 2013/6/28 Lefteris <lsidir@gmail.com <mailto:lsidir@gmail.com>> Dear remi, your question is a general SQL question and not a MonetDB specific. MonetDB sql frontend follows the SQL standard so any SQL query that works in any DBMS will work in the same way in MonetDB too. If you have questions on how to formulate correct queries in SQL I would advice you to use one of the many SQL references available in the web. On the other hand, if you ran a correct SQL query against monetdb and you got an error or incorrect results, meaning a possible bug, then please let us know the error / output of MonetDB and we will help you. lefteris On Fri, Jun 28, 2013 at 4:05 PM, Rémi Daniel < rdaniel@inbox.fr <mailto:rdaniel@inbox.fr>> wrote: Hello, I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution : "SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;" I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases. Does anyone have any idea on how I can do that ? Thanks for your help, Remi -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel@inbox.fr <mailto: rdaniel@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org <mailto:users-list@monetdb.org> http://mail.monetdb.org/mailman/listinfo/users-list -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel@inbox.fr <mailto:rdaniel@inbox.fr> 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79 _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list-- [signature_] Rémi Daniel Développeur Web rdaniel@inbox.fr
30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79
users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
participants (5)
-
Lefteris -
Martin Kersten -
Niels Nes -
Rémi Daniel -
Stefan Manegold