how to query this

Hello,

sorry I'm not good at SQL statement.
I have a table, whose stru is like:

id value
1 33
2 987
3 10
4 22
....

I want to get the max value and the corresponding id, using this sql:

select max(value),id from table;

but it won't work.

so what's the correct syntax? Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: how to query this

SELECT MAX(value), id FROM table
GROUP BY id;

Regards

John Daisley
Mobile +44(0)7812 451238
Email john@butterflysystems.co.uk

Certified MySQL 5 Database Administrator (CMDBA)
Certified MySQL 5 Developer
Cognos BI Developer

-----------------------
Sent from HP IPAQ mobile device.



-----Original Message-----
From: Slackli User
Sent: Thursday, September 10, 2009 10:43 AM
To: mysql@lists.mysql.com
Subject: how to query this

Hello,

sorry I'm not good at SQL statement.
I have a table, whose stru is like:

id value
1 33
2 987
3 10
4 22
....

I want to get the max value and the corresponding id, using this sql:

select max(value),id from table;

but it won't work.

so what's the correct syntax? Thanks.

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohn.daisley@butter=
flysystems.co.uk



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: how to query this

John Daisley schrieb:
> SELECT MAX(value), id FROM table
> GROUP BY id;
>
> Regards
>
> John Daisley
> Mobile +44(0)7812 451238
> Email john@butterflysystems.co.uk
>
> Certified MySQL 5 Database Administrator (CMDBA)
> Certified MySQL 5 Developer
> Cognos BI Developer
>
> -----------------------
> Sent from HP IPAQ mobile device.
>
>

I guess what Slackli had in mind was more something like this:
SELECT id, value
FROM table
WHERE value = (SELECT max(value) FROM table);

cheers,
wolfgang


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: how to query this

Thanks all the info.
Just got what I wanted.

2009/9/10 Wolfgang Schaefer :
> John Daisley schrieb:
>> SELECT MAX(value), id FROM table
>> GROUP BY id;
>>
>>
>>
>
> I guess what Slackli had in mind was more something like this:
> SELECT id, value
> FROM table
> WHERE value = (SELECT max(value) FROM table);

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: how to query this

--001485e36bef314b1604733aa9d7
Content-Type: text/plain; charset=ISO-8859-1

This can also help:

select id,value from table order by value desc limit 1;


Cheers

Claudio


2009/9/10 Slackli User

> Thanks all the info.
> Just got what I wanted.
>
> 2009/9/10 Wolfgang Schaefer :
> > John Daisley schrieb:
> >> SELECT MAX(value), id FROM table
> >> GROUP BY id;
> >>
> >>
> >>
> >
> > I guess what Slackli had in mind was more something like this:
> > SELECT id, value
> > FROM table
> > WHERE value = (SELECT max(value) FROM table);
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=claudio.nanni@gmail.com
>
>


--
Claudio

--001485e36bef314b1604733aa9d7--