Menu
I have a table. From that I need to return Max value of a field. If that row is empty or if it doest satisfy some conditions the query will return NULL. I need to return NO ROWS if the max returns a NULL. How can i do it? I have tried IS NULL, IF,COALESCE But I am not able to get what i need.
Sorry, it was my mistake. I dont mean to say the word 'NO ROWS', More clearly i dont want to get any rows(no rows should be returned) if it returns NULL. I apologies again for my mistake.
877
MAX returns NULL when there is no row to select. For character columns, MAX finds the highest value in the collating sequence. MAX is a deterministic function when used without the OVER and ORDER BY clauses.
Well by definition you can't return anything if there are no records. You would have to force the query to always return a resultset. So you can force the issue but it seems this type of thing is more suited to the front end instead of trying to make sql return data when there is no data to return.
You can use COALESCE() along with aggregate function MAX() for this.
ISNULL is an SQL function that replaces NULL values with a specified value. In this case, if the MAX value of your field is null, it replaces it with zero, otherwise it returns the MAX value of your selected field name. MAX is a standard SQL function that (unsurprisingly) returns the largest value in the set.
You have to use HAVING, like this:
SELECT MAX(field)
FROM yourtable
-- GROUP BY somefileds
HAVING MAX(field) is not null
HAVING specifies a condition for values of the aggregated columns.
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
