How Does Order by Clause Works If Two Values Are Equal

How does order by clause works if two values are equal?

In relational databases, tables are sets and are unordered. The order by clause is used primarily for output purposes (and a few other cases such as a subquery containing rownum).

This is a good place to start. The SQL standard does not specify what has to happen when the keys on an order by are the same. And this is for good reason. Different techniques can be used for sorting. Some might be stable (preserving original order). Some methods might not be.

Focus on whether the same rows are in the sets, not their ordering. By the way, I would consider this an unfortunate example. The book should not have ambiguous sorts in its examples.

How order by fetch if column has same values

The SQL documentation does not specify any technique for sorting the rows when the values in the columns are the same. This is because the techniques implemented for sorting rows are different.

You could refer to the question below to understand more.

How does order by clause works if two values are equal?

SQL multiple column ordering

ORDER BY column1 DESC, column2

This sorts everything by column1 (descending) first, and then by column2 (ascending, which is the default) whenever the column1 fields for two or more rows are equal.

SQL Order By: Specific Value First, then Ordering?

Use a case expression to put Needs Response rows first. Then order by date descending:

order by case when status = 'Needs Response' then 0 else 1 end, date desc

SQL ORDER BY multiple columns

Sorting in an ORDER BY is done by the first column, and then by each additional column in the specified statement.

For instance, consider the following data:

Column1    Column2
=======    =======
1          Smith
2          Jones
1          Anderson
3          Andrews

The query

SELECT Column1, Column2 FROM thedata ORDER BY Column1, Column2

would first sort by all of the values in Column1

and then sort the columns by Column2 to produce this:

Column1    Column2
=======    =======
1          Anderson
1          Smith
2          Jones
3          Andrews

In other words, the data is first sorted in Column1 order, and then each subset (Column1 rows that have 1 as their value) are sorted in order of the second column.

The difference between the two statements you posted is that the rows in the first one would be sorted first by prod_price (price order, from lowest to highest), and then by order of name (meaning that if two items have the same price, the one with the lower alpha value for name would be listed first), while the second would sort in name order only (meaning that prices would appear in order based on the prod_name without regard for price).

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

---

Related Topics