469,090 Members | 1,082 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Order by before group by

Hi,

I have a table with three columns: datetime, subject, & author. (There are
more columns, but these are the only relavent ones.)

Some rows will have identical subjects. For each subject, I need to select
the author with the most recent date/time stamp.

Any ideas?
Jul 20 '05 #1
11 4554
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote in message
I have a table with three columns: datetime, subject, & author. (There are more columns, but these are the only relavent ones.)

Some rows will have identical subjects. For each subject, I need to select the author with the most recent date/time stamp.


Yes, upgrade to version 4.1. See my thread "find all products with maximum
version" where I solved the problem of finding product and id with maximum
version number from the product table (which has colums id, product,
version). For your case, replace version with date, product with subject,
id with author.
Jul 20 '05 #2
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote in message
I have a table with three columns: datetime, subject, & author. (There are more columns, but these are the only relavent ones.)

Some rows will have identical subjects. For each subject, I need to select the author with the most recent date/time stamp.


Yes, upgrade to version 4.1. See my thread "find all products with maximum
version" where I solved the problem of finding product and id with maximum
version number from the product table (which has colums id, product,
version). For your case, replace version with date, product with subject,
id with author.
Jul 20 '05 #3
"Siemel Naran" <Si*********@REMOVE.att.net> wrote
Yes, upgrade to version 4.1. See my thread "find all products with maximum version" where I solved the problem of finding product and id with maximum
version number from the product table (which has colums id, product,
version). For your case, replace version with date, product with subject,
id with author.

Works perfectly! Thanks so much! I used max(`datetime`) and group by
`subject` ...didn't need to upgrade.

Thanks again
Graham
Jul 20 '05 #4
"Siemel Naran" <Si*********@REMOVE.att.net> wrote
Yes, upgrade to version 4.1. See my thread "find all products with maximum version" where I solved the problem of finding product and id with maximum
version number from the product table (which has colums id, product,
version). For your case, replace version with date, product with subject,
id with author.

Works perfectly! Thanks so much! I used max(`datetime`) and group by
`subject` ...didn't need to upgrade.

Thanks again
Graham
Jul 20 '05 #5
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote
Works perfectly! Thanks so much! I used max(`datetime`) and group by
`subject` ...didn't need to upgrade.

Wait, no it doesn't work.

SELECT `subject` , `author` , max( `datetime` )
FROM `table`
WHERE 1
GROUP BY `subject`
ORDER BY `datetime` DESC LIMIT 0 , 30

This gives me the most recent date and time for each subject, which is what
I want...but it gives me the OLDEST author.

I'm officially confused :P
Jul 20 '05 #6
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote
Works perfectly! Thanks so much! I used max(`datetime`) and group by
`subject` ...didn't need to upgrade.

Wait, no it doesn't work.

SELECT `subject` , `author` , max( `datetime` )
FROM `table`
WHERE 1
GROUP BY `subject`
ORDER BY `datetime` DESC LIMIT 0 , 30

This gives me the most recent date and time for each subject, which is what
I want...but it gives me the OLDEST author.

I'm officially confused :P
Jul 20 '05 #7

"Experienced but Undocumented" <e0*@removethis.toao.net> wrote
This gives me the most recent date and time for each subject, which is what I want...but it gives me the OLDEST author.


(N.b...this is what Siemel experienced. Unfortunately, I don't have access
to 4.1.)
Jul 20 '05 #8

"Experienced but Undocumented" <e0*@removethis.toao.net> wrote
This gives me the most recent date and time for each subject, which is what I want...but it gives me the OLDEST author.


(N.b...this is what Siemel experienced. Unfortunately, I don't have access
to 4.1.)
Jul 20 '05 #9
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote in message
Wait, no it doesn't work.

SELECT `subject` , `author` , max( `datetime` )
FROM `table`
WHERE 1
GROUP BY `subject`
ORDER BY `datetime` DESC LIMIT 0 , 30
This was one of the versions posted, but it's limited. You can only use it
if you select the group by columns (namely 'subject') and one or more
aggregation columns (namely 'max(datetime)'). But you select 'author' too,
and it's not in the group by. So which author to select -- that of the
maximum datetime, or minimum? What if you were to select both max(datetime)
and min(datetime)? Also, some databases flag this as an error.

This gives me the most recent date and time for each subject, which is what I want...but it gives me the OLDEST author.


Read the post carefully. It says you have to upgrade to 4.1 There are 3
methods: one involving a nested select clause, one involving a correlated
sub-query, and one involving correlated sub-queries with row constructors
(but this is not supported in MySql 4.1 either).

http://dev.mysql.com/downloads/index.html
Jul 20 '05 #10
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote in message
Wait, no it doesn't work.

SELECT `subject` , `author` , max( `datetime` )
FROM `table`
WHERE 1
GROUP BY `subject`
ORDER BY `datetime` DESC LIMIT 0 , 30
This was one of the versions posted, but it's limited. You can only use it
if you select the group by columns (namely 'subject') and one or more
aggregation columns (namely 'max(datetime)'). But you select 'author' too,
and it's not in the group by. So which author to select -- that of the
maximum datetime, or minimum? What if you were to select both max(datetime)
and min(datetime)? Also, some databases flag this as an error.

This gives me the most recent date and time for each subject, which is what I want...but it gives me the OLDEST author.


Read the post carefully. It says you have to upgrade to 4.1 There are 3
methods: one involving a nested select clause, one involving a correlated
sub-query, and one involving correlated sub-queries with row constructors
(but this is not supported in MySql 4.1 either).

http://dev.mysql.com/downloads/index.html
Jul 20 '05 #11
"Siemel Naran" <Si*********@REMOVE.att.net> wrote in message
news:I0Ync.41455\
"Experienced but Undocumented" <e0*@removethis.toao.net> wrote in message Read the post carefully. It says you have to upgrade to 4.1 There are 3
methods: one involving a nested select clause, one involving a correlated
sub-query, and one involving correlated sub-queries with row constructors
(but this is not supported in MySql 4.1 either).

http://dev.mysql.com/downloads/index.html
You said you can't upgrade to 4.1. So here's a similar solution. One of
the solutions was:
select p.id, p.name, p.version
from product p,
(select p2.name, max(p2.version) as version from product p2 group
by p2.name) as pp
where p.name = pp.name and p.version = pp.version
The second select table (select p2.name, max(...) group by ...) creates a
temporary view which holds the product name (or subject in your case) along
with the maximum version (or maximum date in your case). The main select
statement joins to this table to fetch other columns of this particular row
(the row with maximum version for a particular product name). In my example
the other columns are product.id (but in yours it is author).

The thing with a view is the database engine does not actually populate the
view/table, but instead materializes it as necessary. If the right indexes
exist, the database engine would never have to create a temporary table to
hold data. I think for the above an index product(name) is all we need.
But if the right indexes don't exist, the database engine will have to
create a temporary table, but only when called for.

If you can't upgrade to 4.1, you can just as well create your own table. In
view of the above paragraph, note that you may sacrifice some efficiency,
but unless you have many records, it will be just OK:
(select p2.name, max(p2.version) as version from product p2 group
by p2.name) as pp
becomes

create table pp (
transaction_id int(1) unsigned not null,
name varchar(50) not null,
maxversion int(1) unsigned not null
);

insert into pp(transaction_id, name, maxversion)
select '1', name, max(version) from product
group by name;
select p.id, p.name, p.version
from product p,
(select p2.name, max(p2.version) as version from product p2 group
by p2.name) as pp
where p.name = pp.name and p.version = pp.version


becomes

select p.id, p.name, p.version
from product p, pp
where p.name = pp.name and p.version = pp.version and pp.transaction_id =
'1';

The purpose of transaction_id is to isolate different users or the same user
with multiple sessions from one another.


Jul 20 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Scott | last post: by
9 posts views Thread by Bob Cummings | last post: by
4 posts views Thread by Mike Nolan | last post: by
104 posts views Thread by Beowulf | last post: by
3 posts views Thread by beconrad | last post: by
2 posts views Thread by Al Reid | last post: by
15 posts views Thread by Jeroen | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.