Leonardo
Reputation: 3191
SQL Query Order by intercalated
First of all, thanks for your time!
I have a recurrence pattern mapped in my database (e.g. google calendar events), I'm trying to perform a query that sorts results by distance and startDate, but I can't make it work. Here is my query:
select
cast(e.id as varchar),
e.starts_at as startsAt ,
e.ends_at as endsAt,
e.name,
e.description,
e.picture,
e.is_full_day as isFullDay,
e.is_private as isPrivate,
e.show_guest_list as showGuestList,
e.friend_can_invite_friends as friendsCanInviteFriends,
e.timezone_name as timezoneName,
(
select
id
from
event_recurrences er
where
er.event_id = e.id
limit 1) is not null as isRecurring,
case
when p.id is not null
and l.sublocality is not null then CONCAT(p."name", ', ', l.sublocality)
when p.id is not null
and l.sublocality is null then p."name"
when p.id is null
and l.sublocality is not null then CONCAT(l.sublocality, ' - ' , c."name")
else l.formatted_address
end as addressOrPlaceName,
cast(p.id as varchar) as placeId,
cast(e.thumbs as varchar),
case
when null is not null
and null is not null then earth_distance( ll_to_earth(cast(cast(null as text) as double precision),
cast(cast(null as text)as double precision)),
ll_to_earth(l.lat,
l.lng)) / 1000
end as distance
from
recurring_events_for('2022-07-05T20:44:55.016+0000',
'2023-07-05T20:44:55.016+0000',
'UTC-3',
15,
false) e
inner join locations l on
e.location_id = l.id
inner join cities c on
l.city_id = c.id
left join places p on
p.location_id = l.id
where
e.is_private = false
and (null is null
or c.id = cast(cast(null as text) as UUID))
order by
distance,
startsAt;
I get the following results:
id |startsat |distance |endsat |name |description |picture |isfullday|isprivate|showguestlist|friendscaninvitefriends|timezonename|isrecurring|addressorplacename |placeid|thumbs |
------------------------------------+-----------------------+------------------+-----------------------+-------------+------------+----------------------------------------+---------+---------+-------------+-----------------------+------------+-----------+---------------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-05 20:30:00.000| 7814.717238024624|2022-07-05 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-06 20:30:00.000| 7814.717238024624|2022-07-06 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-07 20:30:00.000| 7814.717238024624|2022-07-07 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-08 20:30:00.000| 7814.717238024624|2022-07-08 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-09 20:30:00.000| 7814.717238024624|2022-07-09 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-10 20:30:00.000| 7814.717238024624|2022-07-10 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-11 20:30:00.000| 7814.717238024624|2022-07-11 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-12 20:30:00.000| 7814.717238024624|2022-07-12 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-13 20:30:00.000| 7814.717238024624|2022-07-13 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-14 20:30:00.000| 7814.717238024624|2022-07-14 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-15 20:30:00.000| 7814.717238024624|2022-07-15 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-16 20:30:00.000| 7814.717238024624|2022-07-16 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-17 20:30:00.000| 7814.717238024624|2022-07-17 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-18 20:30:00.000| 7814.717238024624|2022-07-18 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-19 20:30:00.000| 7814.717238024624|2022-07-19 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
9a9fd609-d3c2-45cf-aaff-1917a02aba11|2022-07-05 20:00:00.000| 10397.27140682366|2022-07-05 22:00:00.000|tesgte |asdsadsadsad| |false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| | |
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-05 21:00:00.000| 10397.27140682366|2022-07-05 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-06 21:00:00.000| 10397.27140682366|2022-07-06 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-07 21:00:00.000| 10397.27140682366|2022-07-07 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-08 21:00:00.000| 10397.27140682366|2022-07-08 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-09 21:00:00.000| 10397.27140682366|2022-07-09 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-10 21:00:00.000| 10397.27140682366|2022-07-10 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-11 21:00:00.000| 10397.27140682366|2022-07-11 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-12 21:00:00.000| 10397.27140682366|2022-07-12 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-13 21:00:00.000| 10397.27140682366|2022-07-13 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-14 21:00:00.000| 10397.27140682366|2022-07-14 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-15 21:00:00.000| 10397.27140682366|2022-07-15 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-16 21:00:00.000| 10397.27140682366|2022-07-16 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-17 21:00:00.000| 10397.27140682366|2022-07-17 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-18 21:00:00.000| 10397.27140682366|2022-07-18 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-19 21:00:00.000| 10397.27140682366|2022-07-19 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-05 20:00:00.000|10401.432664731024|2022-07-05 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-06 20:00:00.000|10401.432664731024|2022-07-06 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-07 20:00:00.000|10401.432664731024|2022-07-07 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-08 20:00:00.000|10401.432664731024|2022-07-08 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-09 20:00:00.000|10401.432664731024|2022-07-09 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-10 20:00:00.000|10401.432664731024|2022-07-10 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-11 20:00:00.000|10401.432664731024|2022-07-11 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-12 20:00:00.000|10401.432664731024|2022-07-12 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-13 20:00:00.000|10401.432664731024|2022-07-13 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-14 20:00:00.000|10401.432664731024|2022-07-14 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-15 20:00:00.000|10401.432664731024|2022-07-15 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
d4d5927b-2951-423b-9c21-32beb1aa05ff|2022-07-19 20:00:00.000|10401.432664731024|2022-07-19 22:00:00.000|recorrente |dasdsad | |false |false |false |false |UTC-3 |false |Centro - Guarulhos | | |
As you can see, it repeats closest events, sorting by startsat, then changes the event, do the same. What I need is something like this:
id |startsat |distance |endsat |name |description |picture |isfullday|isprivate|showguestlist|friendscaninvitefriends|timezonename|isrecurring|addressorplacename |placeid|thumbs |
------------------------------------+-----------------------+------------------+-----------------------+-------------+------------+----------------------------------------+---------+---------+-------------+-----------------------+------------+-----------+---------------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
c6a2c94c-04ad-4b11-99ca-235a9be6ece7|2022-07-05 20:30:00.000| 7814.717238024624|2022-07-05 22:30:00.000|evento google|manaus | |false |false |false |false |UTC-3 |false |Chapada - Manaus | | |
9a9fd609-d3c2-45cf-aaff-1917a02aba11|2022-07-05 20:00:00.000| 10397.27140682366|2022-07-05 22:00:00.000|tesgte |asdsadsadsad| |false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| | |
2aa119fc-5528-4b60-b482-7a20b3b38e27|2022-07-05 21:00:00.000| 10397.27140682366|2022-07-05 21:30:00.000|teate |sadsad |8bc62c73-7438-46c6-90c8-b376168f516e.png|false |false |false |false |UTC-3 |false |Pinheiros - São Paulo| |[{"size": "200x200", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b38e27/thumb/8bc62c73-7438-46c6-90c8-b376168f516e_200x200.webp"}, {"size": "400x400", "success": true, "outputFilePath": "events/2aa119fc-5528-4b60-b482-7a20b3b3|
As you can see, it is sorted by closest distance on the same day! Then it repeats on the next day and so on.
Hope I was clear, if not, I can improve my question!
Thanks again
Upvotes: 2
Views: 109
Answers (1)
Mike Organek
Reputation: 12484
If you want to "group" results by day while seeing all rows, add the date as part of the order by.
order by
startsAt::date,
distance,
startsAt;
Upvotes: 1