0

MODIFIED TO ADD INFORMATION:

I realize that there have been many "get two highest" or "get second-highest" SQL questions and answered posted, so I apologize in advance if this question is redundant, but I want to do something a bit different than the other situations, and I need some help getting from A to B. I am a MySQL hobbyist at best, so I'm sure the answer is obvious to some of you.

I have a bunch of rows of baseball player single-season statistics. I want to compare their season with the highest value with their season with the second highest value. I also want to be able to compare the two seasons by subtracting the second-highest from the highest.

I can easily get the highest value using MAX, of course, but this is a big more difficult for a novice like myself.

Thanks for your help so far.

I will simplify the relevant table structure so that it is relevant: 

playerid, Year, Value

Each Player-season is separated by year.

What i want returned from my query is

Player id, 
Year [of Highest Value], 
Value [Highest], 
Year [of Second Highest Value], 
Value [Second-Highest]

I hope that is simple enough and clear. Thanks for any help.

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
devil_fingers
  • 11
  • 3

1 Answers1

3

Without knowing your table structure, you could essentially do:

SELECT score
FROM statstable
WHERE playerID=???
ORDER BY score DESC
LIMIT 2

which would retrieve the two rows with the highest scores, which you can the pull out the scores and subtract in your client.

If you need this highest-next_highest value for user in another query, then it gets a bit more complicated.

Marc B
  • 356,200
  • 43
  • 426
  • 500