Need help with complex sorting in SQL

Question

Hi, I have a complex sorting problem with my SQL statement. I have a table with the following columns.

No   Time    Value  
--   ----    -----
 1   0900    ''
 2   1030    ''
 3   1020    ''
 4   1010    ''
 5   1100    ''
 1   1015    'P'
 2   1045    'P'

I want to sort this table by doing the following steps.

1. Select rows from the table where Value is '' (empty string) and sort it by No.

2. Select rows from the same table where Value is 'P' and then sort it by time.

3. Select each row from 2) and insert into 1) by time.

The result should be something like this.

No   Time    Value  
--   ----    -----
 1   0900    ''
 1   1015    'P'
 2   1030    ''
 3   1020    ''
 4   1010    ''
 2   1045    'P'
 5   1100    ''

How can I do this in SQL?

Answer 1

Edit: thanks for comments.

On rereading, I don't think part 3 of your question makes sense. The result from step 1) is not sorted by time, and you cannot insert in it by time.

For example, in your example result, the second row is has time 1015, that is between 0900 and 1030. But it could also be between the 1020 and 1010 rows further on?

Answer 2

It seems like you can express your need in a simpler need.
You always sort by time, possibly also by Value then by no.

    sort by time, value, no

This will sort everything by time. For two identical times, the sorting on value will be applied, etc.

---

You could sort on a unique number, that you build to combine your criteria. But that would be more complex.

Answer 3

Unfortunately, I don't think you can do this with a standard SQL query, and the reason is that your algorithm is not set-oriented. Your sample dataset illustrates this -- you have the first 'P' record showing up between the 0900 and 1030 records, but it would be just as appropriate to put it between the 1010 and 1045 records based on your criteria. If it's correct to have it in the position you show, you need to modify your condition to be something like "place each row from #2 between the first two rows in #1 that bracket it in time", where "first" is defined by the sorting criteria of #1.

The upshot is that this type of setup will likely force you into a cursor-based solution. You might be able to avoid this if you can identify a composite value to order upon, but based on what you have above I don't see what that might be.