Question Re: Ordering distinct statement in Technology Q&A

Ordering distinct statement

Baron — Thu, 04 Jul 2024 09:24:17 GMT

Is it possible to sort a distinct statement based on another column (rather than the distinced one)?

For Example I have this table:

create or replace table mycities (cityid int, cityname varchar(40));
insert into mycities values
(4, 'Vienna'),
(2, 'Cairo'),
(1, 'Yerevan'),
(2, 'Cairo'),
(3, 'Berlin');

And I want to execute something like this, but I get an error.

select distinct cityname from mycities order by cityid;

Re: Ordering distinct statement

regdomaratzki — Thu, 04 Jul 2024 09:34:17 GMT

If you have a distinct clause in the query, the order by clause can only include columns in the query's select list. From the documentation for the -854 error :

Probable cause You specified a function or column reference in the ORDER BY clause that is semantically invalid. For example, for DISTINCT queries the ORDER BY clause may only refer to items in the query's SELECT list.

Reg

Re: Ordering distinct statement

regdomaratzki — Thu, 04 Jul 2024 09:46:14 GMT

How could the query below be properly sorted if this restriction did not exist?

create or replace table mycities (cityid int, cityname varchar(40));
insert into mycities values 
(1, 'Cairo'),
(2, 'Vienna'),
(3, 'Cairo'),
select distinct cityname from mycities order by cityid;

Re: Ordering distinct statement

VolkerBarth — Thu, 04 Jul 2024 10:22:25 GMT

What order would you expect, in the general case the other column (cityid) would have different values for identical cityname values?

In your particular case, as you have simply duplicates in the table, a derived table with distinct does the trick:

select cityname
from
   (select distinct cityid, cityname from mycities) DT
order by cityid;

In the general case, you might need to apply an aggregate like MIN or MAX or the like on the "other column" and group by the "distinct column" to specify an order, such as in this sample with city names in different countries

create or replace table mycities2 (cityname varchar(40), countryname varchar(40));
insert into mycities2 values
('Vienna', 'Austria'),
('Cairo', 'Egypt'),
('Yerevan', 'Armenia'),
('Cairo', 'USA'),
('Berlin', 'Germany'),
('Berlin', 'USA'); -- (*)

select cityname
from
   (select cityname, min(countryname) as min_countryname
    from mycities2
    group by cityname
    ) DT
order by min_countryname;

-- (*) could have used Canada but it's been re-named to Kitchener for obvious  reasons 😉

Re: Ordering distinct statement

Baron — Thu, 04 Jul 2024 10:47:16 GMT

the first approach is great!

I did not expect that this line would generally work (without the need of group by):

select distinct cityid, cityname from mycities

I did not also know that there is another Berlin in the USA 😉

Re: Ordering distinct statement

regdomaratzki — Thu, 04 Jul 2024 11:01:40 GMT

Both Volker and I came up with examples on why you're getting the error, but Volker went the extra step and showed how to use a derived table in the query that forced the decision on how to sort the results.

Extra points for attempting to bring in the location of the Watcom headquarters in Kitchener/Waterloo into the example, but history seems to have gotten in the way. 🙂

Re: Ordering distinct statement

Baron — Thu, 04 Jul 2024 11:24:41 GMT

Using city/country names is always dangerous and it leads to another discussions 😉

I dont know why I used City names in my example, but I liked the first solution of Volker because it is simple and applies to my case.

Re: Ordering distinct statement

VolkerBarth — Thu, 04 Jul 2024 11:26:28 GMT

I did not also know that there is another Berlin in the USA 😉

Oh, there are quite a few...

In contrast, I wasn't aware of the English name of the Armenian capital, this forum is helpful for anyone. 🙂

Re: Ordering distinct statement

jack_schueler — Fri, 05 Jul 2024 10:26:24 GMT

I was surprised to see that Volker knew something of the history of our sister city Kitchener. Our family owns a Pequegnat mantel clock that was made in Berlin (Ontario, Canada) circa 1912. Since Waterloo and Kitchener are glued at the hip, there have been many calls over the years that the two cities should be united. Perhaps they'll rename the combined cities "Berlin". 🙂