CODEWITHSUNDEEP
mysqlsqlgroup-bymin
Dario Rusignuolo
Dario Rusignuolo

Reputation: 2186

calculate the min of a table grouped by a unique id - mysql

I need a help with a query.

select *,min(cast(hour(now())- hour_offer) as unsigned))  as time_left 
       from out_of_offer 
       group by offer_id 
       order by time_left asc

I have multiple hour_offer in my out_of_offer table and I need to get only one record (grouped by offer_id) that is closest to the current hour.

for example

offer_id           hour(now())            hour_offer
    1                 11                   8
    2                 11                   9
    1                 11                   10
    2                 11                   11
    2                 11                   12

what I want my query to do is to get the 3rd and 4th rows because they are the closest to the current hour (11) and they have different offer_ids.

hour_offer is mediumint(2) unsigned

can anybody help me how to do that?

EDIT if I print out hour(now())-hour_offer in the mysql query the result is an huge (positive) number.

Upvotes: 0

Views: 99

Answers (1)

Minoru
Minoru

Reputation: 1730

Try something like this:

SELECT * FROM out_of_offer o,
(SELECT offer_id, MIN(ABS(hour-hour_offer)) AS diff FROM out_of_offer
GROUP BY offer_id) x
WHERE o.offer_id = x.offer_id AND ABS(o.hour-o.hour_offer) = x.diff;

I guess is better use ABS isntead of CAST. I don't know exactly why, but your syntax on CAST is wrong.

You may change de hour for hour(now). I used hour to test the query on SQLFiddle.

Upvotes: 1

Related Questions