CODEWITHSUNDEEP
sqlsqlite
Alex
Alex

Reputation: 461

SQL MAX Statement returning wrong column

I have a DB that looks like

Year     Name      TR     AST
1        Player 1  10     3
2        Player 1  23     "-"
3        Player 1  35     5
1        Player 2  32     1
2        Player 2  29     "-"
3        Player 2  50     2
1        Player 3  74     3
2        Player 3  23     1
3        Player 3  93     9

I am trying to return the players name and the amount for the MAX TR. So in this case it would be Player 3 with 93 TR.

I am using this SQL query within SQLITE

SELECT MAX(TR), name
FROM database
WHERE TR != "-"

It returns the correct MAX(TR) but it's giving the wrong Player Name. 

 93 Player 1

I've run other queries like MAX(AST) and it also gives a wrong Player Name. The same Player Name is returned regardless of which MAX query I use which is where the issue is.

I've also tried this trying to follow the order of SQL execution from here http://www.bennadel.com/blog/70-sql-query-order-of-operations.htm

FROM database
WHERE TR != "-"
SELECT MAX(TR), name

As well as

SELECT name, MAX(TR)
FROM database
GROUP BY name
LIMIT 1

And it returns the wrong TR but with the right corresponding name for that TR.

In case it's an issue I had to use 

WHERE TR != "_"

otherwise it would return a player name and the TR "-". I'm not exactly sure why this is either.

Is there any issue with the order of my query statement?

Upvotes: 0

Views: 287

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The easiest way to get what you want is using order by and limit:

SELECT name, TR
FROM database
WHERE TR <> '-'
ORDER BY TR DESC
LIMIT 1;

You don't even need the max().

Upvotes: 1

Matteo Pampana
Matteo Pampana

Reputation: 166

The MAX is an aggregate function in SQL. You cannot use in a SELECT statement an aggregate function and a non-aggregate element, such as a column like in your case.

Instead of doing that try this solution:

SELECT name, TR 
FROM database
WHERE TR = (
   SELECT MAX(TR)
   FROM database
   WHERE TR != "-"
)

Upvotes: 0

pscuderi
pscuderi

Reputation: 1554

I would use a subquery, probably something like this:

SELECT
    name,
    TR
FROM
    database
WHERE
    TR IN (
        SELECT
            MAX(TR)
        FROM
            database
        WHERE
            TR != "-"
    )

Note that this can give you multiple results when there are multiple players the same TR, which is also the max TR.

Upvotes: 0

Related Questions