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:
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, Adaptive Server 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)
