Database Query - Custom Ordering

General Programming
1

I have a database table that has 3 key fields [DateStart] [DateStopped] and [Reason]

I would like to be able to extract these records in 4 groups (but into the same recordset)

  • DateStopped > TODAY and Reason is NOT blank, ORDERBY = reason. dateSTART
  • DateStopped > TODAY and Reason IS blank, ORDERBY = reason
  • DateStopped < TODAY and Reason is NOT Blank, Orderby = reason, dateStopped
  • DateStopped < TODAY and Reason IS Blank, Orderby = reason, dateStopped

I cannot just select * and Order by Reason, DateStopped, DateStart altough the will at first blush seem to be the obvious… This is for a report, and each of the above condtions is a “section” of the report … so records with “Reason#1” might appear in all 4 sections (with differnt dates etc)

2

Something like that…

SELECT CountryName

FROM dbo.Country

ORDER BY CASE WHEN CountryName = 'INDIA' THEN '1'

WHEN CountryName = 'CHINA' THEN '2'

ELSE CountryName END ASC

3

You should be able to use the UNION statement to join four queries into one result set.

4

Yeah thats is what I thought… but you can’t have individual ORDER BY

here is the original (full) query I thought should have worked

SELECT *
  FROM medications
 WHERE profile_id = '78AD0713-95FB-4DFF-A358-E3EF556C46D6'
   AND date_stopped = '2999-12-31 00:00:00'
   AND reason <> ''
 ORDER BY reason,medication

 UNION
          
    SELECT *
      FROM medications
     WHERE profile_id = '78AD0713-95FB-4DFF-A358-E3EF556C46D6'
       AND date_stopped = '2999-12-31 00:00:00'
       AND reason = ''
     ORDER BY reason,medication
 UNION
          
    SELECT *
      FROM medications
     WHERE profile_id = '78AD0713-95FB-4DFF-A358-E3EF556C46D6'
       AND date_stopped <> '2999-12-31 00:00:00'
       AND reason <> ''
     ORDER BY reason,
date_stopped DESC, medication 

UNION
            
      SELECT *
        FROM medications
       WHERE profile_id = '78AD0713-95FB-4DFF-A358-E3EF556C46D6'
         AND date_stopped <> '2999-12-31 00:00:00'
         AND reason = ''
       ORDER BY reason, date_stopped DESC, medication
5

So, does the query not run (give an error), or just not keep the order? Without creating the tables and data it’s hard for me to quickly test it.

Another possibility is to use row_number() to assign numbers in each query that can be sorted after the entire UNION (if the ORDER BY in each query is actually working). You’d have to add a “prefix” to the row_number function to keep each partition in order.

6

using Unions, the order by is based on the combine output and then order. you can getaway with sub queries by using order by where TOP is used in the select statement. I know this is true for SQL Server, I would need to know which DB Dave is using to confirm.

another thing to add, I have also use ROW_NUMBER() order by to introduce row number. it might be possible to use this as well, where you use ROW_NUMBER() to generate the list the way you want, then put all that in sub query where you can finally sort the list by ROW_NUMBER() value.

7

You could also just dump the rows from each individual query into a temp table, with row_number (or create the temp table first with a separate autoinc key).

8

@jmadren just realize posted about row_number() after I finish adding my comment.

Yes, Temp tables are also another way to go and would work as long as you use row_number() and reference in the select.

Starting to think this could easily fit in a store procedure.

9

Error while Execute Query : ORDER BY clause should come after UNION not before

SQLite doesn’t have stored procedures

This is on an iPhone, and the table will probably have <40 records

10

dave,

can you provide me a sample db with test data for me to play with?

11

I’d have to make up fake data, as my test data is personal health information… but thanks

12

Dave,

what about

  1. make tmp table

  2. do 4 commands as
    INSERT INTO temptable SELECT …

  3. then SELECT * FROM tmpT

13

You could possibly add an additional calculated variable to each sql that you would sort on at the end of all the unions but that you would exclude from the printed reports. This would likely work best if the number of reasons is reasonably small (which would allow you to use a Case statement to categorize them) or if they were all of the same length (which you could then concatenate them) along with preceding code for the sql from which they came along with the appropriate start or stopped date for that group

One possible way might have something like:
1st sql: SELECT *, “1” sql_n, reason sort_reason, datestart sort_date
2nd sql: SELECT *, “2” sql_n, reason sort_reason, datestopped sort_date
etc

Then after all the unions
sort by sql_n, sort_reason, sort_date

And exclude sql_n, sort_reason and sort_date from your report

14

Thanks… I’ll look into both those options