CODEWITHSUNDEEP
sqlpostgresqlmax
Phill Pafford
Phill Pafford

Reputation: 85308

Using MAX() as condition

Wanted to know if I could do this without the sub-query

SELECT *
FROM tbl_name
WHERE id = 123456
AND attempts = (
    SELECT MAX(attempts)
    FROM tbl_name
    WHERE id = 123456
)

Wanting to do something like this

SELECT *
FROM tbl_name
WHERE id = 123456
AND attempts = MAX(attempts)

I get this when I try: 

ERROR:  aggregates not allowed

Upvotes: 0

Views: 198

Answers (4)

Matt Donnan
Matt Donnan

Reputation: 4993

You may be able to achieve this with a group by statement:

SELECT A.ID, MAX(A.Attempts)
FROM Table_Name AS A
WHERE A.ID = 123456
GROUP BY A.ID

Or perhaps slightly different:

SELECT A.ID, MAX(A.Attempts)
FROM Table_Name AS A
GROUP BY A.ID
HAVING A.ID = 123456

Upvotes: 0

Andomar
Andomar

Reputation: 238078

You can do that using windowing functions

select  *
from    tbl_name
WHERE   id = 123456
        and 1 = row_number() over (partition by id order by attempts desc)

Upvotes: 4

João Silva
João Silva

Reputation: 91299

That is not possible because the conditions in the WHERE clause are applied to each row individually, whereas MAX refers to an aggregated value, i.e., an aggregation of data from one or more rows.

Upvotes: 1

vc 74
vc 74

Reputation: 38179

Short answer is no, you cannot mix aggregate and non aggregate values

Upvotes: 3

Related Questions