Did you know that ISNULL function can take Query as a parameter ?
I found it tricky to write a single query to achieve a task given by Geetansu Behera. The question can be instantiated as below:
CityID CityName CityDescription
-----------------------------------------------
2 WA Washington
3 CH Chicago
4 OH Ohio
NULL CA California
99 US United States
He wants a query that should return maximum value of CityID if a particular CityDescription all in a single query. Geetansu gave me the first one and I worked it out another way. Many thanks if you could post some new way around:
1) SELECT ISNULL((SELECT CityID FROM City WHERE CityDescription LIKE 'New York') , (SELECT MAX(CityID) FROM City))
OR
2) SELECT CASE WHEN CityID IS NULL THEN MaxCityID ELSE CityID END 'CityID' FROM (SELECT (SELECT CityID FROM City WHERE CityDescription LIKE 'New York') CityID, (SELECT MAX(CityID) FROM City) MaxCityID) B
Comments
Very good tips!!!!
Cheers!
Your friend Pedro
SELECT TOP 1 CityID
FROM City
ORDER BY CASE WHEN CityDescription LIKE '%New York%' THEN 0 ELSE 1 END
, CityID DESC
The ORDER BY clause contains the "preferences" for the row needed. E.g. best would be CityDescription to contain 'New York' but if no such row then size should be over 5M but if no such then ... and if nothing can be satisfied give me last CityID.