PostgreSQL: find rows where a value is equal maximum in subquery-CodePudding

I have a PostgreSQL that works which uses multiple joins, and one column that is the result of a calculation. From the result of that query I need to extract the rows in which that one column is maximal, and there might be many. If there weren't, I could just ORDER BY that_column DESC LIMIT 1; and I found that if I just needed to that on an existing database table, I could just do:

SELECT columns FROM table_name
WHERE that_coulmn = (SELECT MAX(that_column) FROM table_name)

However, that's also not the case. I have a query that is like this:

SELECT z.xyz, (x.aa- x.bb) * y.qq as that_column 
FROM table_1 x
JOIN table_2 y ON x.foo = y.foo
JOIN table_3 z ON y.bar = z.bar
JOIN table_4 w ON w.baz = z.baz
where x.aa IS NOT NULL

Now, this works. Now I need to know how I put that as a subquery and take from that the z.xyz from the rows where that_column is maximal. Any help?

CodePudding user response：

Maybe is this?

SELECT MAX(thatmax.that_column) as theMax FROM (
   SELECT z.xyz, (x.aa- x.bb) * y.qq as that_column 
   FROM table_1 x
   JOIN table_2 y ON x.foo = y.foo
   JOIN table_3 z ON y.bar = z.bar
   JOIN table_4 w ON w.baz = z.baz
   where x.aa IS NOT NULL
) thatmax

CodePudding user response：

if you need to select other columns as your output:

select * from (
  select *, dense() over(order by (x.aa- x.bb) * y.qq desc) as that_column 
  from table_1 x
  join table_2 y ON x.foo = y.foo
  join table_3 z ON y.bar = z.bar
  join table_4 w ON w.baz = z.baz
  where x.aa IS NOT NULL
) t where that_column = 1

CodePudding user response：

I found it!

I took that as a subquery, used rank to rank by thatColumn and then put that in another subquery to take the ones with rank=1

SELECT xyz FROM (
    xyz, thatColumn, RANK() OVER(ORDER BY thatColumn DESC) FROM (
         -- the subqery in the question
    )
) as highest_and_rank
WHERE thatColumnRank=1

Thank you to everyone who tried to help. You're awesome.