How to vertically flip the column values from top to bottom in SQL which are not in any specific order(neither asc nor desc)?
Example: Table named ‘Country’ has a single column c1 with values
| C1 | --------- | JAPAN | | NEPAL | | INDIA | ---------
Now I want to display the Result-set as below:
| C1 | ---------- | INDIA | | NEPAL | | JAPAN | ----------
If anyone can kindly suggest?
Advertisement
Answer
It’s not possible using standard SQL (at least before 2003) because when not explicitly ordered using ORDER BY, a resultset is an “unordered set”.
This means that the order you’ll get your results is supposed to be totally random and may vary from a call to a given query to the next call to this same query. Most of the time, though, you’ll simply get them the way they have been inserted into the database, but this is not guaranteed. When using advanced RDBMs, you may for instance get your entries sorted according to the last ORDER BY you did before.
That’s also why you don’t get any row number when querying a table. Because it just doesn’t make sense.
However, since SQL 2003, OLAP functions have been introduced, including ROW_NUMBER(). For the reasons exposed above, it has to be “windowed” using an OVER clause, which breaks initial order. But you still can lean on a subquery to fetch them all, joining aside a column that will never vary:
WITH subquery(c1,c2) AS (SELECT C1,1 AS C2 FROM yourtable)
SELECT c1
FROM subquery
ORDER BY row_number() OVER (PARTITION BY c2) DESC
Don’t use RANK() as this will return the same “ex-æquo” row number if two rows are identical.