I have a small problem with returning SQL data in a set order back to my C# program. I'm using Visual Studio 2005 and SQL server 2005 Express Edition (also the SQL Server Management Studio Express tool for testing my SQL commands).

I have opened a Reader from my C# program to my database (works OK) and I now need to read back some data sorted in a specific way.

My database has a table of depart times (stored as text, not strictly time formatted) which I need to sort in a specific way. I want all times (24 hour clock) that are >=0300 and in time sorted order. Afterwards, I want all times that are <0300 also in time sorted order. For example:

SELECT * FROM MyData WHERE Depart BETWEEN 0300 AND 2359 ORDER BY Depart ASC, Arrival ASC

SELECT * FROM MyData WHERE Depart BETWEEN 0000 AND 0259 ORDER BY Depart ASC, Arrival ASC

I want to be able to retrieve data from both SQL commands in a single data reader without executing each SQL statement and retrieving the data separately (it may be that the second statement might not return data in some cases).

I tried to use the UNION command between the two statements to return everything, but because the ORDER BY statement is used, the UNION command returns an error:

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'UNION'.

How can I return the data from both of these statements in a single pass?

Each command executes perfectly when executed on it's own. If I place the UNION command between the two statements and remove the 'ORDER BY Depart ASC, Arrival ASC', it returns the data I want, but sometimes the data is not stored in the SQL database in time order, THATS why I use the ORDER BY to try and fix the order of the data.

Can anyone help me please?

Thanks,

Sean

I'm not sure what you mean? The returned data must be in the order of times 0300 to 2359, THEN in the order 0000 to 0259.

Sometimes, the second portion of the SQL statements I mentioned in my initial posting will not return anything. In this case, only values between 0300 and 2359 will be returned anyway!

I cannot find a syntax suitable to do this. I tried to follow what you meant but I couldn't get anywhere!

Hi,

This still won't work. The ending ORDER BY would sort all the times of the entire result into order. It wouldn't return the 0300-2359 times first, followed by the 0000-0259 times.

Also, when I perform that syntax without the ORDER BY commands, the pre 0300 times still appear before the 0300 times.

It's a matter of returning all 0300-2359 times first in their sorted order, then followed by the 0000-0259 times in their sorted order.

Nice try though!

Sounds like you want to use Multiple Active Resultsets, which is similar to what Dylan Morley is suggesting.

If you want to, you can execute both statements in one go to retrieve 2 resultsets. Eg;

SELECT * FROM CODBA.formats; SELECT * FROM CODBA.sor_deal_types

Then you can use as follows...

   1:

You could try this...

SELECT 1 ROWSET, a.* FROM MyData a WHERE Depart BETWEEN 0300 AND 2359

UNION

SELECT 2 ROWSET, b.* FROM MyData b WHERE Depart BETWEEN 0000 AND 0259

ORDER BY 1, Depart ASC, Arrival ASC

I recommend you store the times as int or decimal. Then your sorting expression would be: ( Depart + 2100 ) % 2400 in a single hit to the database. You can further customize that expression as ( Depart + 2400 - @Cutoff ) % 2400.

Thanks Gary,

This worked a treat. The previous way I was doing this was very long-winded. I built a temporary table, added all the values to that, then I added all the values from a second query to the table and set my reader to work with that.

This way, the SQL code is MUCH shorter and it does the job I want!

Sean