470,619 Members | 1,783 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

find all products with maximum version

Hi.

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?

And which solutions are MySql specific, and which are ANSISql (for
maximum portability I want to be as close as possible to ANSISql)?

And are there other solutions, even including a new datamodel design?
# works in MySql
# fails in SQLAnywhere (but change int(1) to int, remove unique index
and it works)
create table product (
id int(1) unsigned not null auto_increment,
name varchar(50) not null,
version smallint(1) not null,
primary key (id),
unique index theproduct (name, version)
);

# fails in MySql as syntax error
# fails in SQLAnywhere as subquery only allowed on one column
select p.id, p.name, p.version
from product p
where row(p.name, p.version) = (
select p2.name, max(p2.version)
from product p2
group by p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
select p.id, p.name, p.version
from product p
where p.version = (
select max(p2.version)
from product p2
where p.name = p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
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
Thanks.
Jul 20 '05 #1
8 2806

"Siemel Naran" <na*******@excite.com> wrote in message
news:3d**************************@posting.google.c om...
Hi.

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?


Wouldn't the following work?

select name,max(version) from product group by name;
or
select id,name,max(version) from product group by name;
Regards,

Mike Chirico
Jul 20 '05 #2

"Siemel Naran" <na*******@excite.com> wrote in message
news:3d**************************@posting.google.c om...
Hi.

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?


Wouldn't the following work?

select name,max(version) from product group by name;
or
select id,name,max(version) from product group by name;
Regards,

Mike Chirico
Jul 20 '05 #3
"Mike Chirico" <mc******@comcast.net> wrote in message
news:J6qdnX5fUa_LQxTdRVn-
"Siemel Naran" <na*******@excite.com> wrote in message

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?


Wouldn't the following work?

select name,max(version) from product group by name;
or
select id,name,max(version) from product group by name;


The first works if you want to find just the product name and maximum
version.

The second does not work in MySql. The problem is that select id selects
the id of the the first record with name X. Thus if my table is

id name version
1 X 1
2 X 2
3 Y 1
4 X 3

The select id, name, max(version) group by name does the following in MySql
4.0.18

id name version
1 X 3
3 Y 1

Note that the first row for product name "X" picks id=1 but should pick
id=4.

That's why the more complex solutions in my post.

I've added mailing.database.sql-general back to the newsgroup list because
your solution may be correct, just that MySql is not doing it right. But my
ATT news server does not this newsgroup, so I can't crosspost from this
account.

So more generally, in

select X, Y, max(Z)
from ...
group by X

will the selected Y be the Y of any record that has X equal to the grouped
by value? Or will Y be the Y of the record that has the max(Z) in this
group? But then how would the SQL engine deal with avg(Z) or sum(Z); which
Y would it select?

It could also be a syntax error because in ANSISql select columns must
appear in the group by clause, or be used in statistical functions like max,
sum, etc. Though not fully sure about this statement.
Jul 20 '05 #4
"Mike Chirico" <mc******@comcast.net> wrote in message
news:J6qdnX5fUa_LQxTdRVn-
"Siemel Naran" <na*******@excite.com> wrote in message

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?


Wouldn't the following work?

select name,max(version) from product group by name;
or
select id,name,max(version) from product group by name;


The first works if you want to find just the product name and maximum
version.

The second does not work in MySql. The problem is that select id selects
the id of the the first record with name X. Thus if my table is

id name version
1 X 1
2 X 2
3 Y 1
4 X 3

The select id, name, max(version) group by name does the following in MySql
4.0.18

id name version
1 X 3
3 Y 1

Note that the first row for product name "X" picks id=1 but should pick
id=4.

That's why the more complex solutions in my post.

I've added mailing.database.sql-general back to the newsgroup list because
your solution may be correct, just that MySql is not doing it right. But my
ATT news server does not this newsgroup, so I can't crosspost from this
account.

So more generally, in

select X, Y, max(Z)
from ...
group by X

will the selected Y be the Y of any record that has X equal to the grouped
by value? Or will Y be the Y of the record that has the max(Z) in this
group? But then how would the SQL engine deal with avg(Z) or sum(Z); which
Y would it select?

It could also be a syntax error because in ANSISql select columns must
appear in the group by clause, or be used in statistical functions like max,
sum, etc. Though not fully sure about this statement.
Jul 20 '05 #5
na*******@excite.com (Siemel Naran) wrote in message news:<3d**************************@posting.google. com>...
Hi.

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?

And which solutions are MySql specific, and which are ANSISql (for
maximum portability I want to be as close as possible to ANSISql)?

And are there other solutions, even including a new datamodel design?
# works in MySql
# fails in SQLAnywhere (but change int(1) to int, remove unique index
and it works)
create table product (
id int(1) unsigned not null auto_increment,
name varchar(50) not null,
version smallint(1) not null,
primary key (id),
unique index theproduct (name, version)
);

# fails in MySql as syntax error
# fails in SQLAnywhere as subquery only allowed on one column
select p.id, p.name, p.version
from product p
where row(p.name, p.version) = (
select p2.name, max(p2.version)
from product p2
group by p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
select p.id, p.name, p.version
from product p
where p.version = (
select max(p2.version)
from product p2
where p.name = p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
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
Thanks.


Why select the id column as this is causing what looks like an
unnecessary problem If you do not need the id column then

SELECT name, max(version)
FROM product
GROUP BY name

would then work
Hope this helps

Duncan
Jul 20 '05 #6
na*******@excite.com (Siemel Naran) wrote in message news:<3d**************************@posting.google. com>...
Hi.

I have a table of products and versions, and these 2 columns are the
unique key.

My challenge is to write a SQL statement to find the latest version of
each product. My solutions below seem correct, yet MySql complains
that the syntax is incorrect. How to fix?

And which solutions are MySql specific, and which are ANSISql (for
maximum portability I want to be as close as possible to ANSISql)?

And are there other solutions, even including a new datamodel design?
# works in MySql
# fails in SQLAnywhere (but change int(1) to int, remove unique index
and it works)
create table product (
id int(1) unsigned not null auto_increment,
name varchar(50) not null,
version smallint(1) not null,
primary key (id),
unique index theproduct (name, version)
);

# fails in MySql as syntax error
# fails in SQLAnywhere as subquery only allowed on one column
select p.id, p.name, p.version
from product p
where row(p.name, p.version) = (
select p2.name, max(p2.version)
from product p2
group by p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
select p.id, p.name, p.version
from product p
where p.version = (
select max(p2.version)
from product p2
where p.name = p2.name
);

# fails in MySql as syntax error
# works in SQLAnywhere
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
Thanks.


Why select the id column as this is causing what looks like an
unnecessary problem If you do not need the id column then

SELECT name, max(version)
FROM product
GROUP BY name

would then work
Hope this helps

Duncan
Jul 20 '05 #7
"Siemel Naran" <na*******@excite.com> wrote in message
news:3d**************************@posting.google.c om...
# fails in MySql as syntax error
# fails in SQLAnywhere as subquery only allowed on one column
select p.id, p.name, p.version
from product p
where row(p.name, p.version) = (
select p2.name, max(p2.version)
from product p2
group by p2.name
);
Still fails in 4.1. Row constructors must not be supported in this version.

# fails in MySql as syntax error
# works in SQLAnywhere
select p.id, p.name, p.version
from product p
where p.version = (
select max(p2.version)
from product p2
where p.name = p2.name
);
Works in 4.1.

# fails in MySql as syntax error
# works in SQLAnywhere
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


Works in 4.1.
Jul 20 '05 #8
"Siemel Naran" <na*******@excite.com> wrote in message
news:3d**************************@posting.google.c om...
# fails in MySql as syntax error
# fails in SQLAnywhere as subquery only allowed on one column
select p.id, p.name, p.version
from product p
where row(p.name, p.version) = (
select p2.name, max(p2.version)
from product p2
group by p2.name
);
Still fails in 4.1. Row constructors must not be supported in this version.

# fails in MySql as syntax error
# works in SQLAnywhere
select p.id, p.name, p.version
from product p
where p.version = (
select max(p2.version)
from product p2
where p.name = p2.name
);
Works in 4.1.

# fails in MySql as syntax error
# works in SQLAnywhere
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


Works in 4.1.
Jul 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Raed Sawalha | last post: by
24 posts views Thread by Rob R. Ainscough | last post: by
5 posts views Thread by Sunny123 | last post: by
9 posts views Thread by Satish Itty | last post: by
4 posts views Thread by Salad | last post: by
7 posts views Thread by laredotornado | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.