CODEWITHSUNDEEP
sqlpostgresql
Alim S. Ajiev
Alim S. Ajiev

Reputation: 1

Simple query hangs PostgreSQL

I have a table "the_table" with three fields: "id" (primary key), "a" and "b". I need select for each "a" the row where "b" has max value for that "a". Then I run the query:

select x.a, x.id
from the_table x
where b = ( select max( b ) where a = x.a )

This query hangs my PostgreSQL server.

I tried another query:

select x.a, max( x.id )
from the_table x
where b = ( select max( b ) where a = x.a )
group by x.a

But the result is the same.

What is wrong in these queries?

Thanks for any reply!

Upvotes: 0

Views: 2425

Answers (3)

Grzegorz Grabek
Grzegorz Grabek

Reputation: 980

If you want to see only one record for each value in "a" then Postgres has wonderfull DISTINCT ON clause for this.

select distinct on (a) id, a, b 
  from the_table t
 order by a, b desc, id

The order is important - first goes the list of your columns in "distinct on" clause - in your case just "a" column, then order your data how you want to see them - in your case you want to see max b so "b desc"

If you want to return all records where "b = max(b)" for "a" then

select id, a, b
  from (select id, a, b, rank() over(partition by a order by b desc) rnk
          from the_table) a
 where rnk=1

And a good tip - NEVER, EVER use nested SELECTS in WHERE statements. If DB has more then few records you will wait for ages to see results if not something worse.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

I suspect the problem with alias & of course from clause :

select x.*
from the_table x
where x.b = ( select max(x1.b) from the_table x1 where x1.a = x.a );

And of course, if you want just maximum id rather than other information, then use group by clause instead :

Upvotes: 0

Golden Ratio
Golden Ratio

Reputation: 349

You would need a from clause in your subquery.

select x.a, x.id
from the_table x
where b = (select max( b ) from the_table x where a = x.a )

Upvotes: 0

Related Questions