Simple query hangs PostgreSQL

Question

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!

It doesn't hang, it waits for the rest of the query because you have not terminated it yet with a semi-colon. — Daniel Vérité
– Daniel Vérité, Commented Aug 21, 2018 at 13:16
You are missing a ; at the end of your statement. If you are using psql note that the prompt changed indicating that the statement is not finished and it's waiting for you to end it. — user330315
– user330315, Commented Aug 21, 2018 at 13:51

Grzegorz Grabek · Accepted Answer · 2018-08-21 13:49:40Z

1

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.

edited Aug 21, 2018 at 13:49

answered Aug 21, 2018 at 13:42

Grzegorz Grabek

1,0007 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alim S. Ajiev Over a year ago

Thank You very much! I will use the first solution

Golden Ratio · Accepted Answer · 2018-08-21 13:05:16Z

0

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 )

answered Aug 21, 2018 at 13:05

Golden Ratio

3492 silver badges14 bronze badges

2 Comments

Alim S. Ajiev Over a year ago

I am sorry. I simplified the query but have not tested it. The real query was: select ch.loan, max( ch.id ) from cache_Loan_Sum_Per_Hour ch where stat_datetime = ( select max( stat_datetime ) from cache_Loan_Sum_Per_Hour where loan = ch.loan ) group by ch.loan

Alim S. Ajiev Over a year ago

And the other query was: select ch.loan, ch.id from cache_Loan_Sum_Per_Hour ch where stat_datetime = ( select max( stat_datetime ) from cache_Loan_Sum_Per_Hour where loan = ch.loan )

Yogesh Sharma · Accepted Answer · 2018-08-21 13:15:06Z

0

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 :

edited Aug 21, 2018 at 13:15

answered Aug 21, 2018 at 13:07

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Simple query hangs PostgreSQL

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related