find all products with maximum version

[Siemel Naran]

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.

[Mike Chirico]

"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

[Mike Chirico]

"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

[Siemel Naran]

"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.

[Siemel Naran]

"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.

[duncan]

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

[duncan]

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

[Siemel Naran]

"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.

[Siemel Naran]

"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.