Set Order Of column by Sequence sql

Question

If I have a table like this:

Id Rnk
1   1
1   1 
1   2
1   2

and I want to arrange the table like that:

Id Rnk
1   1
1   2
1   1
1   2

And I want it to be fixed so when ever I'll select the table the order will be like this. Any Help on how can I do it?

Answer 1

And I want it to be fixed so when ever I'll select the table the order will be like this.

Quick answer: it cannot be done. You have to always use ORDER BY clause in the query if you want to get rows in desired order.

---

A few related questions and answers on this topis:

• What is the default order of records for a SELECT statement in MySQL?
• Default row ordering for select query in oracle
• SQL: What is the default Order By of queries?
• MySQL, ORDER BY insertion order, no sorting columns

---

Quote from the Wikipedia: Order by

ORDER BY is the only way to sort the rows in the result set. Without this clause, the relational database system may return the rows in any order. If an ordering is required, the ORDER BY must be provided in the SELECT statement sent by the application.

---

Another quote from the Wikipedia: Relational database

The relational model specifies that the tuples of a relation have no specific order and that the tuples, in turn, impose no order on the attributes.

---

In order to get this concrete order you can use row_number analytic functions in this way:

SELECT "Id", "Rnk"
FROM ( 
  SELECT t.*,
         row_number() over (partition by "Id", "Rnk" order by "Id", "Rnk") as rn
  FROM Table1 t
) x
ORDER BY "Id", rn

A demo for PostgreSQL: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=0b86522f37e927a86701e778006e8cad

row_number is now supported by most databases, even MySql will have this feature in the upcoming version