How do I use order by in two columns in the same table? Postgresql

Asked

Viewed 900 times

0

Hello, I have a problem and would like your help. I am beginner with database and need to make a report. The report is of all budgets organized by city and by date. Wanted more or less like this:

Abatiá - 01/01/2019
Abatiá  - 02/01/2019
Abatiá  - 03/01/2019
Botucatu - 01/01/2019
Botucatu - 02/01/2019

I have this sql that I take all the information I need, but I can only order by city, the date is not ordered, how do I solve this?

The date field is of the Timestamp type

SELECT id
      ,title
      ,description
      ,city
      ,created 
  FROM budget 
 WHERE created BETWEEN TO_DATE('01/01/2019', 'dd/mm/yyyy')
   AND TO_DATE('30/12/2019','dd/mm/yyyy') 
 GROUP BY budget.city, budget.id 
 ORDER BY budget.created, budget.city ASC
sql postgresql

  • If you want to sort first by city and then by date then put this field order in the ORDER BY clause. Another thing is that you could simplify using: created BETWEEN DATE '2019-01-01' AND DATE '2019-12-30'.

  • I tried to do this, the city works, is in alphabetical order, but the date does not order, in the result of the query it starts in July 2019, it was to start December 2018.

  • Reverse order: ORDER BY budget.city ASC, budget.created ASC

  • @Benilson happens the same way I told my friend up there, the city is fine, but the date starts in July, it was to start in December last year.

  • @anonimo opa, did not know this abbreviation, I’m already using here, thank you. Now I just need to order the date that is 100%

  • The query Where will not allow this, the filter is from 01/01/2019 until 30/12/2019, will not allow date less than 2019.

  • @Benilson opa, it’s true, my fault. But the ordination starts at 07/29 and soon the bottom has one on the date 01/03

  • In the same city or in different cities?

  • @Benilson When I order only by date first ORDER BY budget.city ASC, budget.created ASC the city is right, but the date does not order. Already when I use ORDER BY budget.created ASC, budget.city ASC the date begins in January, only the city does not order.

  • @anonimo type, I need to know how many budgets had in Abatiá, for example, in the month of January, soon after the next city that begins with B in January too, until the end of the month of January. And so on, Cities with A in fevereiro, afterward B in fevereiro and such..

  • @Jonasgabriel Your created field is of which type ?

  • @Christianoribeirosoares is TIMESTAMP

  • Well, then it’s not a direct ordination by city and date in the period. First you want to sort by month/year, then by city name and finally by date (or just the day).

  • @anonymo exactly, first per month (only that year) and then by city name

Show 9 more comments

1 answer

1


If the field created is effectively TIMESTAMP then use

SELECT id, title, description, city, created 
FROM budget WHERE created::DATE BETWEEN '2019-01-01'::DATE AND '2019-12-30'::DATE 
ORDER BY date_trunc('month', created), city, created;

or:

WHERE created BETWEEN TIMESTAMP '2019-01-01 00:00:00' AND TIMESTAMP '2019-12-30 23:59:59'

  • Wow, perfect, the second WHERE was better than the first in my case. Thank you very much!

Browser other questions tagged sql postgresql

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