bloopiebloopie bloopiebloopie - 7 months ago 22
SQL Question

MySQL is returning a different value then the max value in the sample code provided

In the playertracker table, I want the result to display the deck name associated to the row that has the highest value where the player id (PID) is equal to a given value. Instead a the first deck name is returned along with the largest amount.

create table players(
pid int(10),
pname varchar(20),
favcard varChar(20),
mstplayed varChar(20),
lestplayed varChar(20)
);

create table playertracker(
pid int(10),
deckName varchar(20),
amount int(10)
);

Insert into players
values(1, 'joe', 'swim', 'jump', 'fall'),(2, 'jane', 'up', 'jump', 'fall'),(3, 'jack', 'up', 'jump', 'fall'),
(4, 'joe', 'up', 'all', '5'),(5, 'joe', 'up', 'red', 'fall');

Insert into playertracker
values('1','jump','2'),('1','up','4'),('1','swim','9'),('1','fall','9'),
('2','jump','8'),('2','up','4'),('2','swim','1'),('2','fall','1'),
('3','jump','1'),('3','up','8'),('3','swim','9'),('3','fall','4'),
('4','jump','9'),('4','up','8'),('4','swim','1'),('4','fall','1'),
('5','jump','1'),('5','up','4'),('5','swim','4'),('5','fall','8'),
('6','jump','4'),('6','up','9'),('6','swim','1'),('6','fall','1');


select deckname, max(amount) from playertracker where pid = 1;


Link to the offending SQL

Answer

This is query but return two row

select deckname, amount from playertracker where pid = 1
 and amount = (select max(amount) from playertracker where pid = 1) ;

because

swim    9
fall    9

so

select deckname, amount from playertracker where pid = 1
 and amount = (select max(amount) from playertracker where pid = 1)
 limit 1 ;