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.
8  2951 
"Siemel Naran" <na*******@exci te.com> wrote in message
news:3d******** *************** ***@posting.goo gle.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?
Wouldn't the following work?
select name,max(versio n) from product group by name;
or
select id,name,max(ver sion) from product group by name;
Regards,
Mike Chirico
"Siemel Naran" <na*******@exci te.com> wrote in message
news:3d******** *************** ***@posting.goo gle.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?
Wouldn't the following work?
select name,max(versio n) from product group by name;
or
select id,name,max(ver sion) from product group by name;
Regards,
Mike Chirico
"Mike Chirico" <mc******@comca st.net> wrote in message
news:J6qdnX5fUa _LQxTdRVn- "Siemel Naran" <na*******@exci te.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(versio n) from product group by name;
or
select id,name,max(ver sion) 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.databas e.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.
"Mike Chirico" <mc******@comca st.net> wrote in message
news:J6qdnX5fUa _LQxTdRVn- "Siemel Naran" <na*******@exci te.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(versio n) from product group by name;
or
select id,name,max(ver sion) 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.databas e.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. na*******@excit e.com (Siemel Naran) wrote in message news:<3d******* *************** ****@posting.go ogle.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 na*******@excit e.com (Siemel Naran) wrote in message news:<3d******* *************** ****@posting.go ogle.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" <na*******@exci te.com> wrote in message
news:3d******** *************** ***@posting.goo gle.com... # 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" <na*******@exci te.com> wrote in message
news:3d******** *************** ***@posting.goo gle.com... # 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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Raed Sawalha |
last post by:
I have following XML:
How can I find the depth of XML?
in other word max level of the XML
<Root> ----- level 0
<Name>----- level 1
<FirstName>name</FirstName> ---- level 2
<LasName>lname</LastName>
<MiddleName>mName<MiddleName>
</Name>
|
by: Daniel |
last post by:
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data in the format
that I need it in order to create the reports that we use. So far this
has proven to be successful for the reports that I am doing and...
|
by: Rob R. Ainscough |
last post by:
I was reading yet another book on .NET - VB 2005 Professional (wrox) and
read the statement;
"Microsoft has staked their future on .NET and publicly stated that
henceforth almost all their research and development will be done on this
platform. It is expected that, eventually, almost all Microsoft products
will be ported to the .NET...
|
by: Sunny123 |
last post by:
hello
i am trying to find the maximum value of a function and where it occur.
i.e there is an array
x |y
=============
|
|
|
by: mktselvan |
last post by:
Hi,
Existing running oracle application 11i (11.5.8)
Database version is 8.1.7.4
There is any command / way to know the number of concurrent users for this application.
select SESSIONS_MAX,
SESSIONS_WARNING,
| | |
by: Satish Itty |
last post by:
How do I write the following c# code in vb
Product FindProduct(string code)
{
List<Productproducts = getProducts();
return products.Find(delegate(Product bo)
{ return bo.Code == code; });
}
|
by: Salad |
last post by:
I have a situation where some, not all, users get the message "Couldn't
find file "F:\AccessApps\AppName.mdw". This file is required for startup".
My app the users are attempting to access is written A2003 and they use
the A2003 runtime to access the application. They use a desktop icon
that specifies the location of Access, the Appname,...
|
by: raylopez99 |
last post by:
I would like to know if there's a quick "Linq" way to find the index
of an array having a particular value. I can do this the long way by
sequential iteration, but would like to know if there's a shortcut.
Specifically, you have an Array, say an array of Ints. You have a
maximum value, i.e. int someValue = Array.Max(); and you would like...
|
by: laredotornado |
last post by:
Hi,
I'm using PHP 5 with MySql 5. I have a MySQL InnoDB table with a
column of type INTEGER UNSIGNED. Is there a constant in PHP to insert
the maximum value possible into the column? The underlying OS is Red
Hat Linux, but I'm not sure about the version.
Any help you can provide is appreciated, - Dave
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
