Biswa Biswa - 3 years ago 169
SQL Question

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

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download