Search code examples
sqlsql-servermaxcorrelated-subquery

Return all rows with MAX value based on the calculation done on two columns

I have a table named 'Houses' which contains fields 'house_id', 'house_type', 'bhk_detail', 'bed_count', 'furnishing_type', 'Beds_vacant'. Now i need to write a query to get the house details of the house having highest occupancy which bed_count - bed_vacant. I tried something like this:

SELECT hs.house_id, hs.house_type , hs.bhk_details, hs.bed_count , hs.furnishing_type, hs.Beds_vacant,
       max(hs.bed_count - hs.Beds_vacant
FROM Houses as hs 
GROUP BY hs.house_id, hs.house_type, hs.bhk_details, hs.bed_count, hs.furnishing_type, hs.Beds_vacant 
HAVING MAX(hs.bed_count - hs.Beds_vacant)IN (SELECT max(hs.bed_count - hs.Beds_vacant) FROM Houses as hs)

The query worked for me but i was wondering if we can write it more precisely

Solution

  • I think the simplest way is top 1 with ties:

    select top (1) with ties h.*
from Houses h
order by (h.bed_count - h.beds_vacant) desc;