All geek questions in one place

Using a separate column and doing order on another column gives an error

I have a table: abc_test with columns n_num, k_str.

This request does not work:

select distinct(n_num) from abc_test order by(k_str)

But it works:

  select n_num from abc_test order by(k_str)

How do the DISTINCT and ORDER BY keywords work internally so that the result of both queries changes?

+8
source share
11 answers

As far as I understood from your question.

different: - means select individual (all selected values ​​must be unique). order By: - simply means ordering the selected rows as per your requirement.

The problem is in your first query. For example: I have a table

 ID name 01 a 02 b 03 c 04 d 04 a

now the select distinct(ID) from table order by (name) query is confused about which record it should take for ID-04 (since it has two values, d and a in the Name column). Therefore, the problem for the DB mechanism is here when you say sort by (name) .........

+11
source

Instead, you might consider using a group:

 select n_num from abc_test group by n_num order by min(k_str)
+4
source

The first request is not possible. Let's explain this with an example. we have this test:

 n_num k_str 2 a 2 c 1 b

select distinct (n_num) from abc_test is

 2 1

Select n_num from abc_test order by k_str is

 2 1 2

What do you want to return

select distinct (n_num) from abc_test order by k_str ?

it should return only 1 and 2, but how to arrange them?

+3
source

You select the collection highlight (n_num) from the result set from your query. Thus, the actual relationship to the k_str column is no more. N_num can be of two lines, each of which has a different value for k_str. Thus, you cannot order the collection excellent (n_num) by k_str.

0
source

According to SQL standards, a SELECT can refer to either clauses ("aliases") in the top SELECT level or to columns of the result set in order, and therefore your queries will meet the requirements.

It seems that Oracle, like other SQL implementations, allows you to refer to columns that existed (logically) just before being projected into the SELECT . I'm not sure if such flexibility is so good: IMO is a good practice to expose the sort order to the calling application by including columns / expressions, etc. In the SELECT .

As always, you need to apply dsicpline to get meaningful results. For your first request, the definition of the order is potentially completely arbitrary. You should be grateful for the mistake;)

0
source

This approach is available on SQL Server 2000, you can select different values ​​from the table and order using another column that is not included in Distinct. But in SQL 2012, this will result in the error "ORDER BY elements should appear in the selection list if the" SELECT DISTINCT "parameter is specified.

So, if you want to use the same function as for SQL 2000, you can use the column number for the order (it is not recommended in best practices).

 select distinct(n_num) from abc_test order by 1

This arranges the first column after receiving the result. If you want the order to be executed on the basis of a different column than a separate one, you must add this column also to the select statement and use the column number for the order.

 select distinct(n_num), k_str from abc_test order by 2
0
source

How to do advanced sorting of key columns

The logical order of operations in SQL for your first query (simplified):

  • FROM abc_test
  • SELECT n_num, k_str i.e. add the so-called extended sort key column
  • ORDER BY k_str DESC
  • SELECT n_num i.e. SELECT n_num remove the extended sort key column from the result.

Thanks to the standard function of the extended SQL collation key column, you can sort something that is not in the SELECT because it is temporarily added to it behind the scenes before ordering, and then deleted again after ordering.

So why does this not work with DISTINCT ?

If we add the DISTINCT operation, it will need to be added between SELECT and ORDER BY :

  • FROM abc_test
  • SELECT n_num, k_str i.e. add the so-called extended sort key column
  • DISTINCT
  • ORDER BY k_str DESC
  • SELECT n_num i.e. SELECT n_num remove the extended sort key column from the result.

But now, with the extended sort key column k_str , the semantics of the DISTINCT operation have been changed, so the result will no longer be the same. This is not what we want, so the SQL standard and all reasonable databases prohibit this use.

workarounds

PostgreSQL has the DISTINCT ON , which can be used here just for this work:

 SELECT DISTINCT ON (k_str) n_num FROM abc_test ORDER BY k_str DESC

It can be emulated with standard syntax as follows if you are not using PostgreSQL

 SELECT n_num FROM ( SELECT n_num, MIN(k_str) AS k_str FROM abc_test GROUP BY n_num ) t ORDER BY k_str

Or just (in this case)

 SELECT n_num, MIN(k_str) AS k_str FROM abc_test GROUP BY n_num ORDER BY k_str

I wrote more about SQL DISTINCT and ORDER BY here .

0
source

My request doesn't exactly match yours, but it is pretty close.

 select distinct a.character_01 , (select top 1 b.sort_order from LookupData b where a.character_01 = b.character_01 ) from LookupData a where Dataset_Name = 'Sample' and status = 200 order by 2, 1
0
source

Have you tried this?

 SELECT DISTINCT n_num as iResult FROM abc_test ORDER BY iResult
-1
source

You can do

 select distinct top 10000 (n_num) --assuming you won't have more than 10,000 rows from abc_test order by(k_str)
-1
source

When I got the same error, I decided to solve it by changing it as

 SELECT n_num FROM( SELECT DISTINCT(n_num) AS n_num, k_str FROM abc_test ) as tbl ORDER BY tbl.k_str
-1
source

Source: https://habr.com/ru/post/906268/

More articles:


All Articles