|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Easy SQL Problemname score ------ ----- jim 343 bob 322 jane 122 I need a SQL query that will return the name and score of the person who has the lowest score: eg. jane 122 SELECT name, min(score) as score FROM tester group by name; What is the best query for the job? http://www.plus2net.com/sql_tutorial/sql_min.php this tutorial seems to be wrong. It doesn't work when i run it. Try,
select * from tester where score = (select min(score) from tester) -- or select top 1 with ties * from tester order by score desc AMB Show quote "Mark Thomson" wrote: > I have a simple table that stores > > name score > ------ ----- > jim 343 > bob 322 > jane 122 > > > > I need a SQL query that will return the name and score of the person who has > the lowest score: > > eg. jane 122 > > > SELECT name, min(score) as score FROM tester group by name; > > What is the best query for the job? > > http://www.plus2net.com/sql_tutorial/sql_min.php > this tutorial seems to be wrong. It doesn't work when i run it. > > > Many Thanks,
I've never heard of the ties keyword before. Cheers. Show quote "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@microsoft.com... > Try, > > select * > from tester > where score = (select min(score) from tester) > > -- or > > select top 1 with ties * > from tester > order by score desc > > > AMB > > "Mark Thomson" wrote: > >> I have a simple table that stores >> >> name score >> ------ ----- >> jim 343 >> bob 322 >> jane 122 >> >> >> >> I need a SQL query that will return the name and score of the person who >> has >> the lowest score: >> >> eg. jane 122 >> >> >> SELECT name, min(score) as score FROM tester group by name; >> >> What is the best query for the job? >> >> http://www.plus2net.com/sql_tutorial/sql_min.php >> this tutorial seems to be wrong. It doesn't work when i run it. >> >> >> ....and for that you to work as you want, remove 'desc'.
Show quote "Mark Thomson" <@@@> wrote in message news:OUd1QFtjFHA.1204@TK2MSFTNGP12.phx.gbl... > Many Thanks, > > I've never heard of the ties keyword before. > > Cheers. > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in > message news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@microsoft.com... >> Try, >> >> select * >> from tester >> where score = (select min(score) from tester) >> >> -- or >> >> select top 1 with ties * >> from tester >> order by score desc >> >> >> AMB >> >> "Mark Thomson" wrote: >> >>> I have a simple table that stores >>> >>> name score >>> ------ ----- >>> jim 343 >>> bob 322 >>> jane 122 >>> >>> >>> >>> I need a SQL query that will return the name and score of the person who >>> has >>> the lowest score: >>> >>> eg. jane 122 >>> >>> >>> SELECT name, min(score) as score FROM tester group by name; >>> >>> What is the best query for the job? >>> >>> http://www.plus2net.com/sql_tutorial/sql_min.php >>> this tutorial seems to be wrong. It doesn't work when i run it. >>> >>> >>> > > You are right.
AMB Show quote "Raymond D'Anjou" wrote: > ....and for that you to work as you want, remove 'desc'. > > "Mark Thomson" <@@@> wrote in message > news:OUd1QFtjFHA.1204@TK2MSFTNGP12.phx.gbl... > > Many Thanks, > > > > I've never heard of the ties keyword before. > > > > Cheers. > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in > > message news:BC89C4FC-E232-4DCA-85B8-FCA33AC303CC@microsoft.com... > >> Try, > >> > >> select * > >> from tester > >> where score = (select min(score) from tester) > >> > >> -- or > >> > >> select top 1 with ties * > >> from tester > >> order by score desc > >> > >> > >> AMB > >> > >> "Mark Thomson" wrote: > >> > >>> I have a simple table that stores > >>> > >>> name score > >>> ------ ----- > >>> jim 343 > >>> bob 322 > >>> jane 122 > >>> > >>> > >>> > >>> I need a SQL query that will return the name and score of the person who > >>> has > >>> the lowest score: > >>> > >>> eg. jane 122 > >>> > >>> > >>> SELECT name, min(score) as score FROM tester group by name; > >>> > >>> What is the best query for the job? > >>> > >>> http://www.plus2net.com/sql_tutorial/sql_min.php > >>> this tutorial seems to be wrong. It doesn't work when i run it. > >>> > >>> > >>> > > > > > > > Mark Thomson wrote:
> I have a simple table that stores I would write something like this:> > name score > ------ ----- > jim 343 > bob 322 > jane 122 > > I need a SQL query that will return the name and score of the person who has > the lowest score: > > eg. jane 122 SELECT name, score FROM tester WHERE score = (SELECT MIN(score) FROM tester) select top 1 name, score from tableName order by score
If course, if 2 people have the same score, this will return only one of them. Show quote "Mark Thomson" <@@@> wrote in message news:OacoT6sjFHA.476@TK2MSFTNGP14.phx.gbl... >I have a simple table that stores > > name score > ------ ----- > jim 343 > bob 322 > jane 122 > > > > I need a SQL query that will return the name and score of the person who > has the lowest score: > > eg. jane 122 > > > SELECT name, min(score) as score FROM tester group by name; > > What is the best query for the job? > > http://www.plus2net.com/sql_tutorial/sql_min.php > this tutorial seems to be wrong. It doesn't work when i run it. >  |
|||||||||||||||||||||||
Other interesting topics