6 votes

Is default ordering affected by clustered index?

here's a demo Yano is of course correct about order not being guaranteed without an order by. This has been written about 6 trillion times across the span of the internet for most if not all of the ...
Erik Darling's user avatar
  • 40.8k
4 votes

Is default ordering affected by clustered index?

Without ORDER BY clause, the order of rows is not guaranteed. It could be the same one day, and different another day. Is default ordering affected by clustered index? Oftentimes, the data are read ...
Yano_of_Queenscastle's user avatar
4 votes

How to query in the given order

First, you need to use the IN operator with multiple values. WHERE options IN (10,223,43,1,23,54,323,32) Secondly, relational databases are agnostic about row order and can return them in any order ...
Paul W's user avatar
  • 608
2 votes
Accepted

Can you make Postgres execute ORDER BY after OFFSET and LIMIT?

You would wrap that into a subquery to display results with a different sort order: SELECT * FROM ( SELECT * FROM users ORDER BY id LIMIT 3 OFFSET 3 ) sub ORDER BY id DESC; ...
Erwin Brandstetter's user avatar
2 votes
Accepted

Why does my LIMITed SELECT fetch the last record of the table, not the first?

The order of the results of a SELECT query are never guaranteed when there's no ORDER BY clause specified. This is true in pretty much all modern relational database systems (as per the SQL standard). ...
J.D.'s user avatar
  • 37.5k
2 votes

Why is Postgres so slow to order those 200 rows that are already ordered?

This appears to be just another (but peculiar) manifestation of the classic ORDER BY...LIMIT problem. Notice the lack of Sort nodes in the first plan, so it is relying on the index scan to read the ...
jjanes's user avatar
  • 39.8k
2 votes
Accepted

Why is Postgres so slow to order those 200 rows that are already ordered?

The added sort causes a completely different query plan, which should not occur, and can be avoided with CTEs if need be. Whatever else you do, simplify the query first. The subqueries serve no ...
Erwin Brandstetter's user avatar
2 votes
Accepted

Oracle MAX gives different result than ORDER BY desc

The result depends on your environmental settings. Example: with some_data as ( select '123A55' as hex from dual union all select '123255' as hex from dual) select * from some_data order by hex desc;...
Balazs Papp's user avatar
  • 40.7k
2 votes
Accepted

Identify if the sequence of visits to an area id was interrupted at any point

Assuming all involved columns NOT NULL, and no duplicate timestamps per area_id. Undefined aspect: what if the same area_id is visited again (after a gap) and it starts over with trip_nr = 1. Still &...
Erwin Brandstetter's user avatar
2 votes

Managing the order of aggregation in Postgres

In the Aggregate functions page (chapter 9, section 20 in postgresql 12 documentation "Aggregate Functions" (1)) there's a paragraph specifically addressing this The aggregate functions ...
Dragas's user avatar
  • 151
1 vote
Accepted

Trying to renumber a field in order grouping by another

For an UPDATE query you don't need to rejoin, you can update the derived table directly UPDATE T SET sequence = RowNum FROM ( SELECT T.*, row_number() OVER (PARTITION BY C.co ORDER ...
Charlieface's user avatar
  • 12.9k
1 vote

ORDER BY slows query performance

Your query is convoluted, that's the primary issue. Straighten it out first: SELECT * FROM act.activity_schedule a WHERE a.customer_id = :customer_id AND a.archived = :archived AND a.deleted =...
Erwin Brandstetter's user avatar
1 vote

Managing the order of aggregation in Postgres

The manual goes on to say: ... Alternatively, supplying the input values from a sorted subquery will usually work. For example: SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; In ...
Erwin Brandstetter's user avatar
1 vote
Accepted

How can I sort by "release" in Mysql, where e.g. 10.10 comes after 10.9, with non-numerical entries both before and after?

SELECT * FROM releases ORDER BY LEFT(ReleaseNumber, 1) <> '_', -- start with '_' - put first ReleaseNumber + 0 = 0, -- non-numeric - put last ...
Akina's user avatar
  • 19.9k
1 vote

MySQL ORDER BY Indexes When ORDER BY May be Unknown

First, simplify the query to SELECT oi.* FROM order_items AS oi JOIN orders AS o ON oi.order_id = o.id WHERE o.deleted_at IS NULL AND oi.deleted_at IS NULL ORDER BY ... ...
Rick James's user avatar
  • 78.1k

Only top scored, non community-wiki answers of a minimum length are eligible