CODEWITHSUNDEEP
mysql
Aaron Kruger
Aaron Kruger

Reputation: 23

SQL Select record with MAX non zero value If exists otherwise select record with zero value

What I would like to do is select a single record per unique timestamp within a given date range from my table. If multiple records exist for the same timestamp I want to get the maximum non zero record, or if only zero valued records are returned I just want to return one of those zero valued records. The query I have been using 'almost' works which is

select timestamp, MAX(value) FROM myTable WHERE *conditions*... group by [timestamp] Order By [timestamp] ASC;

It obviously works well until the returned records are zero's and negative numbers, in which case it returns the zero record since it is greater than the negative records. In that case I would want to return one of the negative value records rather than the zero record. If I try to solve that by changing the where statement to

WHERE [Value] <> 0

then I don't get anything if only zero valued records are returned. My priority is returning a single greatest non zero valued record per timestamp, but if only zero valued records exists then just return one of those, and if only null records exist for the timestamp I would want a single null record returned.

Upvotes: 2

Views: 4957

Answers (2)

AdamMc331
AdamMc331

Reputation: 16690

What I would do is select the maximum non-zero value in the way that you described:

SELECT timestamp, MAX(valueCol) AS maxValue
FROM myTable
WHERE valueCol <> 0
GROUP BY timestamp;

Then, you can use an outer join to get all timestamps, and use the COALESCE() function to replace the values that weren't returned with 0:

SELECT m.timestamp, COALESCE(t.maxValue, 0) AS maxValue
FROM myTable m
LEFT JOIN(
   SELECT timestamp, MAX(valueCol) AS maxValue
   FROM myTable
   WHERE valueCol <> 0
   GROUP BY timestamp) t ON t.timestamp = m.timestamp;

EDIT: Based on your comments and the edit to your question you can break up the problem in the following way. I wrote a query (seen above) to get the maximum non-zero value for each timestamp. I can also write a query to get the timestamps where the maximum/minimum timestamp is 0 like this:

SELECT id, 0 AS MaxVal
FROM myTable
GROUP BY id
HAVING MAX(valueCol) = 0 AND MIN(valueCol) = 0;

The remaining group will have a value of null. To get those, I can do the same outer join as my previous example, but leave out the COALESCE() function so that null values remain as such:

SELECT DISTINCT m.id, t.maxVal
FROM myTable m
LEFT JOIN(
  SELECT id, MAX(valueCol) AS maxVal
  FROM myTable
  WHERE valueCol <> 0
  GROUP BY id
  UNION ALL
  SELECT id, 0 AS maxVal
  FROM myTable
  GROUP BY id
  HAVING MIN(valueCol) = 0 AND MAX(valueCol) = 0) t ON t.id = m.id;

I added the DISTINCT keyword to return unique id/maxValue pairs, because multiple rows will be returned otherwise since the id appears multiple times in the same table.

I tested this in SQL Fiddle and it worked great.

Upvotes: 1

Random Developer
Random Developer

Reputation: 1344

Give this a try, change @ValueCol to 1, 0, null works fine. 

DECLARE @ValueCol INT 
SET @ValueCol = Null

Select CASE WHEN MAX( CASE WHEN @ValueCol IS NULL THEN 1 ELSE 0 END) = 0  THEN MAX(@ValueCol) END

so that would make your SQL look like this from the original post.

select 
timestamp
, CASE WHEN MAX( CASE WHEN value IS NULL THEN 1 ELSE 0 END) = 0  THEN MAX(value) END
FROM myTable 
WHERE *conditions*... 
group by [timestamp] 
Order By [timestamp] ASC;

Upvotes: 0

Related Questions