order by and group by Used with select distinct

A select distinct query with order by or group by can return duplicate values if the order by or group by column is not in the select list.

For example, the following query orders results by pub_id:
select distinct pub_id
from titles
order by type
pub_id
------
0877
0736
1389
0877
1389
0736
0877
0877
 
(8 rows affected)

If a query has an order by or group by clause that includes columns not in the select list, SAP ASE adds those columns as hidden columns in the columns being processed. The columns listed in the order by or group by clause are included in the test for distinct rows. To comply with ANSI standards, include the order by or group by column in the select list. For example:

select distinct pub_id, type
from titles
order by type
pub_id type 
------ ------------
0877   UNDECIDED 
0736   business 
1389   business 
0877   mod_cook 
1389   popular_comp
0736   psychology
0877   psychology
0877   trad_cook
 
(8 rows affected)