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
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
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
this 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
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
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
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
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
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
), 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
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
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,
| 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
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]
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
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
-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
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
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
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
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
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]
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
]
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
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
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
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
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
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
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
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
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
32 matches
Mail list logo
