5
Suppose I make one SELECT sort of like this:
SELECT * FROM "users" ORDER BY "createdAt";
In this context, if I have data that has the same value in the field createdAt, how SQL will sort these fields?
P.S.: I know that if there is another field in the clause ORDER BY "solve" this problem.
P.P.S.: I want to know how SQL reacts to this kind of situation. Will it always show the same order? Or is it kind of random, if there is such a "collision" in the values of the ordering clause?
3
Since he didn’t use a tie-breaker he’ll look for the easiest way possible for that query, which may even vary depending on a number of criteria, ie if you want a specific or repeated order then be explicit in which you will use, do not count on a specific order if you do not specify this order. Doesn’t mean it’ll be random, it’s just not clearly determined.
There is nothing in the SQL specification that determines this, each database can do as you wish, and in fact can change from version to version.
1
The pattern SQL does not guarantee that the recovered data has a standard ordering. Without a ORDER BY specific, the order of its results will always be undetermined.
In the PostgreSQL, the order of columns which are not part of the clause ORDER BY is determined by the manner in which the planner/optimizer decided to create the result set.
Simple queries like SELECT * FROM tabela are likely to be returned in the same order in which they were stored on the disk, and may be ordered by the primary key, in the order in which they were inserted, or in any other random order.
The rule is: NEVER trust the order pattern, it can change from night to day! Use at all times the clause ORDER BY whether sorting data is important to you.
References:
https://stackoverflow.com/questions/8746519/sql-what-is-the-default-order-by-of-queries
https://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order
Browser other questions tagged sql postgresql query order-by
You are not signed in. Login or sign up in order to post.
if it is not possible to group, select would return as it normally would without the group, depending on the database, if the table has Dice, etc.. but in general, it is equal to the result of the select without group
– Ricardo Pontual
What will happen roughly is that the item (i) will not be larger than the (i+1) so i++
– Motta