SQL with special sort

[former_SQLA_member1694869]

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

Exempel:

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?

[Breck_Carter]

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

[johnsmirnios]

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"

[VolkerBarth]

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.

[former_SQLA_member1694869]

Volker, you understand it exactly.

[Vlad]

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.

[VolkerBarth]

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

[Vlad]

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.

[johnsmirnios]

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.

[VolkerBarth]

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

[Breck_Carter]

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.

[johnsmirnios]

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 🙂

[VolkerBarth]

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

[VolkerBarth · Accepted solution]

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)
values
(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.

[Vlad · Accepted solution]

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

[VolkerBarth · Accepted solution]

Hopefully 🙂

[Vlad · Accepted solution]

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

[VolkerBarth · Accepted solution]

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!

[Vlad · Accepted solution]

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

[johnsmirnios · Accepted solution]

"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?

[VolkerBarth · Accepted solution]

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... 🙂