-2
--------------------------------
|uid |lesson_score |date       |
--------------------------------
|1   |2            |1391023460 |
|1   |4            |1391023518 |
|2   |3            |1391023557 |
|1   |8            |1391023596 |
--------------------------------

How do I determine the maximum and minimum scores for a particular user, and their respective date?

So far I have this: 

SELECT MAX(lesson_score) AS max_lesson_score, MIN(lesson_score) AS min_lesson_score, date
FROM user_progress 
WHERE uid = 1

What I need to get is this:

min: 2, 1391023460
max: 8, 1391023460

I know it's returning the correct min and max score, but it's only returning one date, and I'm not even sure WHICH date it's returning...

Thanks!

user3241112
  • 69
  • 3
  • 8

2 Answers2

0

Your query is not legal SQL, exactly because there are different data values for MIN and MAX scores.

The query is not simple because you want to return MIN and MAX in different rows.

SELECT lesson_score, date 
FROM user_progress
WHERE uid = 1
AND (
      lesson_score = (SELECT MAX(lesson_score) 
                      FROM user_progress
                      WHERE uid = 1)
   OR (lesson_score = (SELECT MIN(lesson_score)
                      FROM user_progress
                      WHERE uid = 1)
  )

And if you have more than one lesson_score equal to MIN or MAX, you will get extra rows showing it.

Joe Pineda
  • 5,521
  • 3
  • 31
  • 40
fredt
  • 24,044
  • 3
  • 40
  • 61
-2

Quick fix:

SELECT MAX(lesson_score) AS max_lesson_score, MIN(lesson_score) AS min_lesson_score, date
FROM user_progress 
WHERE uid = 1
GROUP BY date

Whenever you have an aggregate function (sum, count, max, min), you need to include all non-aggregate columns in the GROUP BY, so it knows which columns to perform those functions on. I hope that makes sense.

Sev09
  • 883
  • 2
  • 12
  • 27
  • 1
    This will return one row for each test date. What if there are 10 tests per user on different dates? – fredt Jan 31 '14 at 17:47