3

How can I select max column value which returns 0 when no rows matching condition and returns null when any row contains the column with null value

For example:

SELECT MAX(groupId) from Interview WHERE appId= 61

When no rows present matching this condition or when there are rows with groupId=null, it returns 0 - when queryForInt(), null - when queryForList() and get the first entry.

Please let me know, either query change or Hibernate method.

carexcer
  • 1,407
  • 2
  • 15
  • 27
Chinmaya
  • 33
  • 4
  • Your first sentence contradicts your second sentence. Maybe you should be thinking about two queries. Or a general query and handle the "business logic" in java. – K.C. Jan 10 '14 at 09:15

1 Answers1

0

You could try:

SELECT CASE MaxGroupId WHEN COUNT(groupId)=0 THEN 0 ELSE MAX(groupId) END FROM Interview WHERE appId=61

SQL If statement help:How do I perform an IF...THEN in an SQL SELECT?

Or Something like:

SELECT CASE MaxGroupId WHEN COUNT(Record_id)=0 THEN 0 ELSE MAX(groupId) END FROM Interview WHERE appId=61 AND Group by ANOTHERCOLUMN

Community
  • 1
  • 1
pavbhaji
  • 62
  • 1
  • 7
  • The problem is that count(groupId) is always returns 0 even when no rows matching and when groupId is null. Anyway thanks for your suggestions. For now I have handled the use case in my code. Thanks all – Chinmaya Jan 15 '14 at 05:50
  • You could use some other column to count on. What other columns are there in your interview table. Is there a non-nll primary key like record id? You could use that. – pavbhaji Jan 15 '14 at 19:37
  • Awesome. It works perfectly..Huge thanks... select CASE WHEN COUNT(id)=0 THEN 0 ELSE MAX(groupId) END as maxGroupId FROM Interview WHERE applicationId=61 – Chinmaya Jan 23 '14 at 17:03