Sort Mysql for the last 3 months

Asked

Viewed 1,429 times

3

I want to make an SQL query ordered for the first 3 months from the current month, example:

Current month is August (08), so I order as:

10
9
8
1
12

I’m using the following command:

SELECT * FROM `tb_convencao` WHERE `lg_historico` = 0 ORDER BY (`desc_database`) >= MONTH(now()) DESC, (`desc_database`) < date_add(MONTH(now()), interval 3 month)

But it’s returned to me:

10
12
8
9
1

Any hint?

php mysql sql order-by

  • The question makes no sense. What is commanded for the last three months? Ordination has no condition. Give more information on how the table is composed, the content and expected result, and better explain the objective. The order should be by which column? Or does it not even need order? Should there be a filter that only takes the lines from the last 3 months? What types of columns?

  • The field lg_historico is date?

  • Excuse me, the lg_historic and desc_database field are int(11).

  • To order correctly using dates, are field must be date and not int, just changing the data type of the field your problem must be solved.

  • 1

    You can accept an answer if it solved your problem. You can vote on every post on the site as well. Did any help you more? Something needs to be improved?

1 answer

3

Apparently this is what you need but I can’t guarantee that the question is confused:

SELECT * FROM `tb_convencao` WHERE `lg_historico` = 0 AND
    `desc_database` >= MONTH(now()) - 3
    ORDER BY `desc_database` DESC

If you want the next three months:

SELECT * FROM `tb_convencao` WHERE `lg_historico` = 0 AND
    `desc_database` >= MONTH(now()) AND
    `desc_database` <= MONTH(now()) + 3
    ORDER BY `desc_database` DESC

I put in the Github for future reference.

If you want to show the next 3 months first and then everything else, make a selection with these months and then another selection with the others. If you want everything to be in single query, just use one UNION.

  • is almost that... Me is returned: desc_database 12 - 10 - 9 - 8 - 2 - 1 However as it is in August, I wanted it to be ordered by November (11), so the 12 would not be ordered in the first position.

  • I made a slip, see if solved. But only with the past information it is difficult to help more than this.

  • I expressed myself badly in the same question, sorry. Actually with Where does not work for me, because the other values should also appear in the list. Only the point is that I want the values data_base >= MONTH(now())+3 at the top of the list

  • Sorting is not filter, you cannot filter the sorting. Either you will have to filter or you will have to make more than one selection to get the rest. Or even better explain what you want.

Browser other questions tagged php mysql sql order-by

You are not signed in. Login or sign up in order to post.