CODEWITHSUNDEEP
mysqlsqlsql-order-bymyisam
Juno J
Juno J

Reputation: 197

How "ORDER BY" ordered if they are same value?

Let's say there's table:

name | score
A      100
B      98
C      99
D      99
E      99
F      98

and request sql = 'select * from table order by score desc'

I know order's gonna be 100, 99, 99, 99, 98, 98 but there are several values in 99 and 98.

I'm using MyIsam and want to make sure the order in the 99 and 98 is not changed even though I delete some element. For example, if the order was C->D->E and I delete D, then I expect to be C->E but no E->C. Is there any logic it works when there's same order value?

Upvotes: 5

Views: 11945

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

SQL tables represent unordered sets. So, there is no "default" ordering when order by keys have the same value.

Technically, this means that sorting in SQL is unstable. The fix is to add one or more additional keys so each combination is unique.

For your sample data, this can presumably be handled using name:

order by score desc, name

You can also use any other column(s) if available that uniquely identify each row. These keys go after the score.

Upvotes: 1

Shreyas Kumar
Shreyas Kumar

Reputation: 109

SQLite stores row in a table in an unspecified order. It means that the rows in the table may or may not be in the order that they were inserted.

If you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified. To sort the result set, you add the ORDER BY clause in the SELECT statement as follows:

SELECT
   select_list
FROM
   table
ORDER BY
    column_1 ASC,
    column_2 DESC;

I hope this link may help you.

Upvotes: 0

Tharuka Madumal
Tharuka Madumal

Reputation: 201

SELECT * FROM table ORDER BY score DESC

Above query ordering the data only by the score. If you want to order the data by another field as you mentioned, you have the add that field also to the query as below. You have mentions that you want to order by name in acceding order (C > D >E). So I used ORDER BY ASC. Below query will give you the output that you requested.

SELECT * FROM table ORDER BY score DESC,name ASC

Upvotes: 2

Related Questions