0

I have a query which successfully returns the top three test results for a student as follows.

TestID | PersonID | Subject | Score 
----------------------------------- 
   1        1       Maths      92 
   2        1       Science    88 
   3        1       English    82

I want to nest my current query to show the data as follows?

PersonID | BestSubject | 2ndBestSubject | 3rdBestSubject 
--------------------------------------------------------- 
   1     |   Maths     |   Science      |    English

I could nest my current query three times, but it seems inefficient given the information is there after running it once.

Is there a more efficient way to do this?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Terence Sweeney
  • 25
  • 7
  • 1
    This is "pivot". Not supported by MySQL. Emulate. – Akina Aug 10 '20 at 13:56
  • Okay. I have edited the question to include... "I could nest my current query three times, but it seems inefficient given the information is there after running it once. Is there a more efficient way to do this?" – Terence Sweeney Aug 10 '20 at 14:05
  • read: https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql – Luuk Aug 10 '20 at 14:05
  • What version of MySQl are you using? If it supports window functions, you could use row_number() in the subquery to rank the scores then use conditional aggregation in the outer query to pivot them – Radagast Aug 10 '20 at 14:12
  • @TerenceSweeney You question is closed so I can't answer, but you could also try https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=25464fb13baaeaf4561619465b91145b – Radagast Aug 10 '20 at 14:39
  • Thanks Isildur, This is exactly what I wanted. It is a pity the question was closed. The other answers did not fit my needs nearly as well as yours. I think others would benefit from seeing this. – Terence Sweeney Aug 11 '20 at 04:14

0 Answers0