CODEWITHSUNDEEP
mysql
Jack Sierkstra
Jack Sierkstra

Reputation: 1434

Get the earliest time if rows are more than 1

I need help with some MySQL pain in the ****... Anyway, i got the following sql :

SELECT id,count(*),
 CASE 
    WHEN count(*) > 1 THEN
        // I need the minimal `taskdate_time` column from the selected rows
        // where a certain boolean is active 
 ELSE taskdate_time
 END
FROM timehistory th
WHERE `send`=true
GROUP BY date_format(taskdate_time, "%Y-%m-%d"), user_id

As described in the comments, i need to get the earliest time out for the two rows where a column called removed is not FALSE

How do i achieve this?

My columns are :

`id` - int
`taskdateuser_id` int
`user_id` int
`changed_by` int
`batch_id` int
`taskdate_time` timestamp
`send` tinyint
`isread` tinyint
`update` tinyint
`removed` tinyint

Many thanks in advance!!!

EDIT:

I might explain it a bit more. If i got the following table rows :

enter image description here

The red marked rows are captured by the CASE count(*) > 1, because there are 2 rows returned by the group by. Then i need to to a SELECT from that 2 captured rows where removed=false and min(taskdate_time). So if 4 rows are returned for that group by, and 2 of the rows are removed=false and the other are removed=true then i need to do a subselect for the minimum taskdate_time that 2 rows where removed=false.

Upvotes: 0

Views: 65

Answers (2)

sgeddes
sgeddes

Reputation: 62851

You could try something like this:

SELECT TH.user_id, COUNT(*), 
  CASE WHEN COUNT(*) > 1 
  THEN MIN(IF(TH.removed, TH.taskdate_time, NULL))
  ELSE TH.taskdate_time
  END   
FROM TimeHistory TH 
...

Sample Fiddle Demo

However, if COUNT > 1 AND there aren't any records where TH.removed is true, then this will return NULL for that value. What should it return in those cases?

--EDIT--

In response to comments, then this should work just wrapping it with COALESCE:

  COALESCE(
    CASE 
    WHEN COUNT(*) > 1 
    THEN MIN(IF(TH.removed, TH.taskdate_time, NULL))
    ELSE TH.taskdate_time
    END, MIN(TH.taskdate_time))

Upvotes: 0

John Woo
John Woo

Reputation: 263813

SELECT  id,
        count(*),
        CASE WHEN count(*) > 1 
             THEN (SELECT MAX(taskdate_time) FROM timehistory f WHERE f.id = th.id AND removed = 0)
             ELSE taskdate_time
        END
FROM    timehistory th
WHERE   `send` = true
GROUP   BY date_format(taskdate_time, "%Y-%m-%d"), user_id

Upvotes: 1

Related Questions