Geek asks and answers

Should ORDER BY affect the number of rows returning a SELECT query?

I am experiencing the pain of normalizing a terrible old database and found that I consider a mistake with the DBMS.

This query returns the results as I expect:

SELECT DISTINCT RIGHT(SQUEEZE(thing_id), 2) AS thing_id, TRIM(thing_name)
FROM thing
ORDER BY thing_id, thing_name;
(16 rows)

The first time I ran the query, I accidentally used the wrong columns in ORDER BY, as shown below:

SELECT DISTINCT RIGHT(SQUEEZE(thing_id), 2) AS thing_id, TRIM(thing_name)
FROM thing
ORDER BY thing_name, location;
(33 rows)

Note that the only thing you need to change is ORDER BY, and the number of rows returned is increased from 16 to 33. The results are not DISTINCT, as the query indicates.

I believe that this is a complete mistake, but a colleague says that this is normal, because when we order by "location", it is selected to be included invisibly in the results.

Should ORDER BY affect the number of rows returned in a SELECT query?

EDIT: , . , diff. 100% , - , ORDER BY.

: Ingres 14301 : " 126640 (GENERIC). , . ."

. , .

+5
4

SQL:

FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

. ?

+3

, , , (location) ORDER BY, SELECT DISTINCT. SQL ( , , -, Enres). ( ):

( SQL):

SELECT DISTINCT 
      thing_id 
    , thing_name
FROM thing
ORDER BY thing_id
       , thing_name ;

( SQL, ):

SELECT DISTINCT 
      thing_id 
    , thing_name
FROM thing
ORDER BY thing_name
       , location;

? ORDER BY SELECT DISTINCT. , thing_id thing_name, location. . , . ( ), ?

SELECT DISTINCT SELECT ALL GROUP BY ( ):

SELECT ALL
      thing_id 
    , thing_name
FROM thing
GROUP BY thing_id 
       , thing_name
ORDER BY thing_name
       , location;

( 2) PostgreSQL, SQL-Server Oracle. SQL-Fiddle

, Ingres, , , , , location SELECT, ORDER BY . DISTINCT :

SELECT DISTINCT 
      thing_id 
    , thing_name
   (, location         --- hidden column) 
FROM thing
ORDER BY thing_name
       , location;

, , , , .

, Actian: DISTINCT + ORDER BY . , , ( "" ).

, , , :

SELECT
      RIGHT(SQUEEZE(thing_id), 2)  AS squeezed_thing_id
    , TRIM(thing_name)             AS trimmed_thing_name 
    , MIN(location)                AS a_location            --- or MAX()
FROM 
    thing
GROUP BY 
      RIGHT(SQUEEZE(thing_id), 2)
    , TRIM(thing_name)     
ORDER BY 
      trimmed_thing_name
    , a_location ;
+6

, order by .

, . ( ) , , Ingres.

Edit

, . , , ( Ingres ).

, . , .

+3

location , . , , , location. , , . , SQL ?.

0

More articles:


All Articles