0

NOTE: This is not the same as what others used to close this question. I have now made it clear that I want to use the MAX() function, not an alternative to it. I know very well that I can use other workarounds like ORDER BY + LIMIT, etc. this is not what I want, so don't just close the question again and shove it up with other similar questions that are not what I am looking for.

I'm trying to run a query that returns a row result with the max value Y, but I don't want that extra column included in the results.

I've searched SO for a solution for this, but haven't found any that would fit my requirements.

Example query:

    SELECT *, MAX(Score)
    FROM Table1

Result:

Name                       Score    MAX(Score)
Werribee Open Range Zoo      6          6

Snippet example http://sqlfiddle.com/#!5/52b4e/3

My question is, how can I get the above result WITHOUT the max(score) column included?

Note: I am using this query in Android Room library, which is limited in what kind of queries can be done.

Shadow
  • 4,168
  • 5
  • 41
  • 72

1 Answers1

0

You could use

SELECT * FROM Table1 ORDER BY score DESC LIMIT 1

BUT like your original whether Werribee or Johannesburg is returned will be according to the underlying indexing use by the query optimiser. i.e you cannot be sure which.

If you wanted all that have the highest score then you could use:-

SELECT * FROM Table1 WHERE score = (SELECT MAX(score) FROM Table1)
MikeT
  • 51,415
  • 16
  • 49
  • 68