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.

My problem is that I want to sort a list in a special way.


Pos Material    Location    Level
10    AE          Pal1        1
10    AC          Pal2        2
10    AD          Pal3        2
10    AB          Pal3        4
30    AD          Pal1        1
30    AC          Pal2        2
30    AB          Pal3        2
30    AE          Pal4        4
70    AC          Pal2        1
70    AE          Pal3        2
70    AD          Pal4        4
70    AF          Pal4        4
60    AB          Pal1        1
60    AF          Pal2        2
60    AD          Pal3        2
60    AE          Pal4        4
60    AE          Pal5        4

First I want to sort Loaction asc, Material asc on Level 1. Then I want to continue sorting under the respective Pos at other levels. The sorting should be the same, Location asc, material asc.

The result should be as follows:

Pos Material    Location    Level
60    AB          Pal1        1
60    AF          Pal2        2
60    AD          Pal3        2
60    AE          Pal4        4
60    AE          Pal5        4
30    AD          Pal1        1
30    AC          Pal2        2
30    AB          Pal3        2
30    AE          Pal4        4
10    AE          Pal1        1
10    AC          Pal2        2
10    AB          Pal3        4
10    AD          Pal3        2
70    AC          Pal2        1
70    AE          Pal3        2
70    AD          Pal4        4
70    AF          Pal4        4

I have tested different solutions, but I doesn't solve it. How on earth do I solve this?

asked 13 May '20, 14:15

Rolle's gravatar image

accept rate: 0%


Please explain how your result set is ordered. Don't use the word "sort" because your result set is not "sorted" on anything, as far as the usual definition of sorting is considered.

60 - 10 - 70 is not sorted

AF - AD - AF is not sorted

Pal1 - Pal 4 - Pal1 is not sorted

1 - 2 - 1 is not sorted

(13 May '20, 15:02) Breck Carter
Replies hidden

I agree that since no column is actually in a sorted order, there is no multi-column ordering that can achieve it. If pos were sorted 10-30-60-70, I think the sort order is achievable with "order by pos, location, material, level"

(13 May '20, 15:53) John Smirnios

In my understanding this is a "derived sort order" - i.e. "pseudo-sort by Location, Material, Pos" for all Level 1 entries and then list all entries with Level > 1 directly after the entry with the same Pos value, and these "derived entries" ordered by Location, Material, Level. Just my understanding.

(14 May '20, 03:37) Volker Barth

Volker, you understand it exactly.

(14 May '20, 05:52) Rolle

When I see your questions like this, I always feel sorry for a poor database.

I'd better move this kind of custom stuff to an application level with a more high-level language.

(14 May '20, 06:32) Vlad
Replies hidden

feel sorry for a poor database

Well, I can't tell whether the actual database schema is fine here or not - but generally SQL should be "better in sorting" than an application, methinks...

(14 May '20, 06:50) Volker Barth

This is not a sorting, what I see here. It is a custom arranging of records based on values from previously already ordered records and something else. I don't think that nested "loops" on the DB server are better than the same nested loops in Java/C#/Go. At least the supportability of no-SQL languages will be much better.

(14 May '20, 06:55) Vlad

I don't think it can be done in a single sort. You cannot simply compare two rows to know if one is greater than the other. For example, how does (30,AD,Pal1,1 ) compare to (60,AF,Pal2,2)? Well, it depends on whatever is in the row (60,??,??,1). The primary sort order for the data above is some ordering of the unique 'pos' values that depends on what other values are in the table. You would need to create a temporary table or some such thing with unique level-1 items (ie, unique (location,material,pos) values where level=1) and use it to create a mapping (let's call it 'pos_ordinal()') that defines the sort order for the 'pos' column -- possibly by going through the table in order and updating a pos_ordinal column. Then sort the original data by pos_ordinal(pos), location, material, level. You would need to think about what happens if you have two different 'pos' values for the same (location, material) values or no level-1 value for some (location, material). If done in an application, much the same approach would need to be done.

(14 May '20, 07:59) John Smirnios

I don't know and don't have the time to try currently, but I guess I'll try later...

(14 May '20, 08:03) Volker Barth

Document your requirements in pseudo-code English, something that would compile and run if you had a pseudo-code English computer.

The goal is to get your human audience to understand what the rules are... that's the first step in finding a solution.

(14 May '20, 08:20) Breck Carter

If time efficiency is not high priority and there are guarantees about exactly one Level-1 row per 'pos' value, one could probably define pos_ordinal( p ) with the following pseudo-SQL:

set l1_loc, l1_mat = select [first?] location, material from T where t.pos = p and t.level = 1

set ord = select count( * ) from T where t.level = 1 and ( t.location < l1_location or (t.location = l1_location and t.material < l1_material ))

return ord

... and that would avoid the temp table. I'm not going to try it nor do I expect you to do so, Volker. It's just ideas for the original poster. I find it an intriguing problem :)

(14 May '20, 08:30) John Smirnios

Well, I just don't agree in this particular case, see my answer, and I guess the query itself is still portable ANSI SQL.

(15 May '20, 02:55) Volker Barth
showing 4 of 12 show all flat view

While I agree that Rolle could have tried harder to clarify the specs - I don't find that too hard to solve - unless I have missed some details.

General assumption: There is one entry with level 1 for every Pos. (If not, one would have to change the CTE accordingly.)

Here you go:

drop table if exists TestPos;
create table TestPos( 
    -- add a PK column just to show how to conserve original order
   PK  int default autoincrement primary key,
   Pos int not null,
   Material varchar(10) not null,
   Location varchar(10) not null,
   Level int not null

-- Sample date
insert TestPos(Pos, Material, Location, Level)
(10, 'AE', 'Pal1', 1),
(10, 'AC', 'Pal2', 2),
(10, 'AD', 'Pal3', 2),
(10, 'AB', 'Pal3', 4),
(30, 'AD', 'Pal1', 1),
(30, 'AC', 'Pal2', 2),
(30, 'AB', 'Pal3', 2),
(30, 'AE', 'Pal4', 4),
(70, 'AC', 'Pal2', 1),
(70, 'AE', 'Pal3', 2),
(70, 'AD', 'Pal4', 4),
(70, 'AF', 'Pal4', 4),
(60, 'AB', 'Pal1', 1),
(60, 'AF', 'Pal2', 2),
(60, 'AD', 'Pal3', 2),
(60, 'AE', 'Pal4', 4),
(60, 'AE', 'Pal5', 4);

-- Check sample data with OP's input:
select Pos, Material, Location, Level
from TestPos
order by PK;

-- Use a CTE to get the order of the entries of Level 1
-- sorted by Location asc, Material asc, as was specified
-- (and by Pos added by me)
-- and use rank() to get a sort criterium,
-- and then join the CTE with the original data on Pos
-- and thereby apply the sort criterium to all entries with the same Pos,
-- then sorting by the further columns
with CTE_FirstLevel as
   (select Pos, rank() over (order by Location, Material, Pos) as Level1Order
    from TestPos
    where Level = 1)
select TP.Pos, TP.Material, TP.Location, TP.Level
from TestPos TP
   inner join CTE_FirstLevel CTE on TP.Pos = CTE.Pos
order by CTE.Level1Order, TP.Location, TP.Material, TP.Level;

As said, I don't think it's that difficult or "something that should be done outside the database"... YMMV.

I would even state that it is a not too rare requirement - in my humble experience at least - to sort some result set based on a criterium not directly shown in the output.

permanent link

answered 15 May '20, 02:42

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 15 May '20, 02:53

Cool - a window function, a join, looks simple. Hopefully Rolle will not come next time with your code and a new requirement tomorrow.

(15 May '20, 02:59) Vlad
Replies hidden

Hopefully :)

(15 May '20, 03:23) Volker Barth

I am afraid that you have done his job, while he was sleeping. I don’t know how much time you have spent to find, write and test the solution.
I am not against solving everything in SQL. Sometimes it is a good exercise for a brain, but not when the final solution becomes non-maintainable anymore. For example, application servers are easy to debug and scale-up, when you have more active users, and the database resources can be used for something important (such as analytics).

(15 May '20, 04:15) Vlad

I don’t know how much time you have spent to find, write and test the solution.

Well, say, around 10 minutes? - When answering, I'm usually trying to combine "being helpful" with "learning for myself", and that was particularly the case here. Of course that may conflict now and then with "doing other's homework." but here I was more interested in checking whether the comments like "not possible in one query" were valid or not...

And to be clear: Contributors like the SQL Anywhere engineers, Breck and myself are apparently mainly focussed on SQL-based solutions. I'm well aware that you, Vlad, often point to different solutions outside SQL databases, and IMHO this is very often a helpful perspective. So please keep on providing those insights!

(15 May '20, 04:37) Volker Barth

Thanks for your comment. On my daily basis I work with people, who built their solutions using different technologies, and quite often I see long-running tasks (e.g. web requests, that fetch data from a database) that use these kind-of-queries with many tables/joins etc. And these queries cause 90% CPU time of the DB server that can be avoided if people just change their architecture & development a bit. You can create indexes, tune hints until a certain point, after that you should better give us and say something like: "maybe we can store these records into Redis and free 70% of the CPU Time?"

Something like that...

(15 May '20, 05:02) Vlad

"not possible in one query" was never claimed. It is true that there is no single multi-column sort of the provided data that can produce the desired results. It requires at two separate sorts and one will generate a mapping to create an ordinal for the 'pos' column of the final sort based on other values in the table. You've generated that mapping and used it via a join with some nice SQL wizardry.

In this SQL query, I suspect that if you are missing a 'level 1' row for a given location & material then the whole section for that 'pos' value will not be reported. And if there are multiple level-1 rows, then 'pos' sections will get repeated (once for each level-1 value). Correct?

(15 May '20, 06:24) John Smirnios

Oops, I apparently misunderstood your statement "I don't think it can be done in a single sort."...

Yes, as stated, the assumption I took from the OP was that there is exactly one entry with Level 1 per Pos value, and there are no entries with "overlapping" sort values. But if this is not true, then I still think the basic query pattern can be adjusted, say to use "Min(Level)" instead of Level 1 within the CTE or to use row_number() instead of rank(). - In order to "not do too much homework", I'd say the rest is left as an exercise for the reader... :)

(15 May '20, 06:51) Volker Barth
showing 1 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 13 May '20, 14:15

question was seen: 926 times

last updated: 15 May '20, 06:52