Mysql get unique records between dates with a specific order

Question

I have the following table

    id | item_id | start_date |  end_date  | disc_percent
    1  |  1      | 2016-12-27 | 2016-12-31 |    50
    2  |  2      | 2016-12-27 | 2016-12-31 |    50
    3  |  3      | 2016-12-27 | 2016-12-31 |    50
    4  | 137     | 2016-12-27 | 2016-12-31 |    50
    5  | 1       | 2016-12-28 | 2016-12-29 |    10

and I am running this query:

SELECT * 
from onsale 
WHERE  (CURDATE() BETWEEN onsale.start_date 
                      AND onsale.end_date) 
order by onsale.start_date DESC, 
         onsale.end_date ASC

I want to get only the first record of each item_id like this:

    id | item_id | start_date |  end_date  | disc_percent
    5  |  1      | 2016-12-28 | 2016-12-29 |    10
    2  |  2      | 2016-12-27 | 2016-12-31 |    50
    3  |  3      | 2016-12-27 | 2016-12-31 |    50
    4  | 137     | 2016-12-27 | 2016-12-31 |    50

how can I get this result? Thanks for the help

Possible duplicate of [ROW\_NUMBER() in MySQL](http://stackoverflow.com/questions/1895110/row-number-in-mysql) — Juan Carlos Oropeza, Dec 29 '16 at 06:50
Possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — shmosel, Dec 29 '16 at 06:51
Just read thru the answers recommended, i dont undestand them — AL DI, Dec 29 '16 at 07:51

score 2 · Answer 1 · answered Dec 29 '16 at 07:04

2

For getting unique record for item_id, you should use group by clause. group by clause returns unique row for a table.

The query will be like this -

SELECT * 
from onsale 
WHERE  (CURDATE() BETWEEN onsale.start_date 
                      AND onsale.end_date) group by item_id
order by onsale.start_date DESC, 
         onsale.end_date ASC

answered Dec 29 '16 at 07:04

Rahul Khandelwal Systematix

141
10

group by does not provide the result i expect – AL DI Dec 29 '16 at 07:51
Do you want the result exactly what you mentioned in the table above and also the record of the table will be fixed ? – Rahul Khandelwal Systematix Dec 29 '16 at 09:35
`ASC` is optional – denny Dec 29 '16 at 10:12
yes i need the exact result and the records are dinamic – AL DI Dec 29 '16 at 17:53

denny · Answer 2 · 2016-12-29T12:25:28.600

0

Try This...No aggregation functions needed...

select * from (select * from onsale order by start_date desc) onsale group by item_id;

check on sqlfiddle:-http://sqlfiddle.com/#!9/218a1/1

edited Dec 29 '16 at 12:25

answered Dec 29 '16 at 12:12

denny

2,084
2
15
19

thanks denny your answer work great on the fiddle but i dont undestand why it does not work on my server, the only different thing i notice is the mysql version fiddle = 5.6 my server = 5.7. any ideas? – AL DI Dec 29 '16 at 22:21
@ALDI i also tried on 5.7.successfully execute.there is no trouble on it.check here http://rextester.com/SVGEOV26849 – denny Dec 30 '16 at 05:22
@ALDI only date different on both result cause you ignore end_date which was past.and today is 30dec. – denny Dec 30 '16 at 05:27

Mysql get unique records between dates with a specific order

2 Answers2