Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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;

asked 04 Jul, 09:24

Baron's gravatar image

Baron
2.2k144152181
accept rate: 48%


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 ;)
permanent link

answered 04 Jul, 10:22

Volker%20Barth's gravatar image

Volker Barth
40.5k365556827
accept rate: 34%

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 ;)

(04 Jul, 10:47) Baron
Replies hidden
2

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. :)

(04 Jul, 11:01) Reg Domaratzki
Replies hidden

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.

(04 Jul, 11:24) Baron
1

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. :)

(04 Jul, 11:26) Volker Barth
1

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". :-)

(05 Jul, 10:26) JBSchueler

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

permanent link

answered 04 Jul, 09:34

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.9k343119
accept rate: 36%

1

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;
(04 Jul, 09:46) Reg Domaratzki
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13
×6

question asked: 04 Jul, 09:24

question was seen: 169 times

last updated: 05 Jul, 10:26