CODEWITHSUNDEEP
phpmysqlsql
libertaire
libertaire

Reputation: 855

sql order by most recent dates

I have this table in my database:

    INSERT INTO `shop_stats` (`date`, `value`) VALUES
('09/2014', 326),
('08/2014', 1007),
('07/2014', 1108),
('06/2014', 1027),
('05/2014', 895),
('04/2014', 650),
('03/2014', 683),
('02/2014', 563),
('01/2014', 499),
('12/2013', 568),
('11/2013', 522),
('10/2013', 371),
('09/2013', 347),
('08/2013', 376),
('07/2013', 418),
('06/2013', 567),
('05/2013', 357);

i need to find a way to display the last 12 months.

I tried this:

SELECT * FROM shop_stats ORDER BY date DESC LIMIT 12

But it doesn't work correctly.

Any suggestions ?

Upvotes: 1

Views: 2859

Answers (6)

Priyank
Priyank

Reputation: 3868

select * from shop_stats where date >= (NOW() - INTERVAL 12 MONTH) ORDER BY date

Upvotes: 0

krishna
krishna

Reputation: 4099

You can simply use STR_TO_DATE like this

SELECT
 *
FROM
 shop_stats 
order by
 STR_TO_DATE(date, '%m/%Y') DESC LIMIT 12

Demo

Upvotes: 0

Logan
Logan

Reputation: 1371

You can take a look at DATE_SUB

SELECT * FROM shop_stats where date >= DATE_SUB(now(), INTERVAL 12 MONTH) ORDER BY date

Edit: How about converting the string to date & doing the appropriate date operations in the sql ? 

SELECT DATE
    ,t1.value
FROM (
    SELECT DATE
        ,STR_TO_DATE(CONCAT (
                '01/'
                ,DATE
                ), '%d/%m/%Y') date_
        ,value
    FROM shop_stats
    ) t1
WHERE t1.date_ >= DATE_SUB(now(), INTERVAL 12 MONTH)
ORDER BY t1.date_ DESC

http://sqlfiddle.com/#!2/2be05/8

Upvotes: 0

Joe Taras
Joe Taras

Reputation: 15379

I suppose your field date has a type string

So you try this:

SELECT * FROM shop_stats 
ORDER BY SUBSTRING(date, 4, 4) desc,
substring(date, 1, 2) DESC LIMIT 12

Show Sql Fiddle

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your "dates" are stored as strings, presumably with the month first. So, the following order by should work

order by right(date, 4), left(date, 2)

You need to put the year before the month for ordering purposes.

If you want the last twelve months, I would recommend:

where right(date, 4) * 12 + left(date, 2) >= year(now()) * 12 + month(now())
order by right(date, 4), left(date, 2)

The where statement converts the dates to a number of months, for both the "date" column in your data and for the current time.

Upvotes: 1

derdida
derdida

Reputation: 14904

SELECT * FROM shop_stats WHERE date >= DATE_SUB(NOW(),INTERVAL 1 YEAR) LIMIT 12

Upvotes: 1

Related Questions