Strange query with some if conditions

Question

Fellow programmers,

I got a table where i have the following columns :

[tablename - usertimes]
id - int(11)
user_id - int(11)
time - timestamp
deleted - tinyint(1)

Imagine i have the following rows :

[id] [user_id]      [time]       [deleted]
------------------------------------------
 1       1     2013-02-25 16:40     0
 2       1     2013-02-25 14:40     0
 3       1     2013-02-25 11:20     1
 4       1     2013-02-26 11:20     1
 5       2     2013-02-23 16:40     0
 6       2     2013-02-25 16:40     0

Now i need to group by date(time), user_id where min(time) i have the minimum time where deleted is not false. BUT!!! If the only row for that date has deleted=true, then it has to return that. so it has to return the following rows :

[id] [user_id]      [time]       [deleted]
------------------------------------------
 2       1     2013-02-25 14:40     0
 4       1     2013-02-26 11:20     1
 5       2     2013-02-23 16:40     0
 6       2     2013-02-25 16:40     0

What would be the best approach for this? Should i just get all rows and check them in my code? I believe that a query must be possible for this but i can't wrap my head around it.

EDIT:

Can somebody please play with this fiddle? : http://sqlfiddle.com/#!2/4f91a

It has the table contents and the desired output commented.

EDIT2:

This is the output according to this fiddle : http://www.sqlfiddle.com/#!2/3808b/1

| ID | USER_ID | DELETED |             TIME |
---------------------------------------------
|  1 |       1 |       0 | 2013-02-25 14:40 |
|  4 |       1 |       1 | 2013-02-26 11:20 |
|  5 |       2 |       0 | 2013-02-23 16:40 |
|  6 |       2 |       0 | 2013-02-25 16:40 |

This is really close to what i want to achieve. Because what i want to achieve is this result notice the first id :

| ID | USER_ID | DELETED |             TIME |
---------------------------------------------
|  2 |       1 |       0 | 2013-02-25 14:40 |
|  4 |       1 |       1 | 2013-02-26 11:20 |
|  5 |       2 |       0 | 2013-02-23 16:40 |
|  6 |       2 |       0 | 2013-02-25 16:40 |

Answer 1

i'm not sure what the heck you are doing, but this query gives you your result

select ut.*
from usertimes ut
join (
select user_id,deleted, min(time) as time
from usertimes
group by date(time), user_id, deleted
) a on a.user_id = ut.user_id and a.time = ut.time
group by ut.user_id, date(ut.time)
order by ut.user_id

Answer 2

Try this SQL Fiddle:

(
SELECT id,user_id,deleted,min(time) as TIME
FROM usertimes
WHERE deleted=0
GROUP BY date(time), user_id
)
UNION
(
SELECT id,user_id,deleted,time FROM(
SELECT *,COUNT(user_id) as cnt_user_id 
FROM usertimes GROUP BY user_id,date(time) 
)a
WHERE a.deleted=1 and a.cnt_user_id =1  
)ORDER BY user_id