OK.
I did not design this data staructure, but I have to make a plan somehow:
Table has lots of columns...but I'll just use a few important ones.
tableID fk1 fk2 orderBy
1 4 NULL 7
2 4 2 6
3 4 NULL 1
4 4 NULL 5
5 2 4 5
6 2 4 2
I can easily query the table to get all rows that have fk1=4 OR fk2= 4
What I cannot do is work out how to order the results, using orderBY column, so that
all results where fk1=4 are listed first, and ordered followed by all results where fk2=4 are listed second, and ordered
eg
tableID fk1 fk2 orderBy
3 4 NULL 1
4 4 NULL 5
2 4 2 6
1 4 NULL 7
6 2 4 2
5 2 4 5
I thought it could be done with 2 SELECTS, ordering them and then using UNION ALL to put them together, but no luck...
SELECT tableID, fk1, fk2 orderBy FROM tablename WHERE tableID IN ( SELECT TOP 100 PERCENT tableID FROM tablename WHERE fk1=4 ORDER BY orderBy ASC
UNION ALL
SELECT TOP 100 PERCENT tableID FROM tablename WHERE fk2=4 ORDER BY orderBy ASC ) ORDER BY ....???
I cannot use ORDER BY fk1 ASC, fk2 ASC or fk1 DESC, fk2 DESC as it I will not know what fk value to order on.
not sure about the syntax of this, or even if it's valid, but can you maybe make your sub queries output a "fake" extra column that you can use for sorting?
e.g. SELECT TOP 100 PERCENT tableID, 1 as extraOrderBy FROM ... UNION ALL SELECT TOP 100 PERCENT tableID, 2 as extraOrderBy FROM ...
? Then you select that extraOrderBy column in your outer select and order on it?
You'd end up with something like this: SELECT tableID, fk1, fk2, extraOrderBy, orderBy FROM tablename WHERE tableID IN ( SELECT TOP 100 PERCENT tableID, 1 as extraOrderBy FROM tablename WHERE fk1=4 ORDER BY orderBy ASC
UNION ALL
SELECT TOP 100 PERCENT tableID, 2 as extraOrderBy FROM tablename WHERE fk2=4 ORDER BY orderBy ASC ) ORDER BY extraOrderBy ASC, orderBy ASC
Apologies if what I've said goes nowhere! It's been a while since I did any heavy SQL.
ah hang on I see I misread your SQL - I thought you were selecting from a temporary table, but you're not, you're matching IDs to a temporary table. SELECT tableID, fk1, fk2, extraOrderBy, orderBy FROM see what I removed here ( SELECT TOP 100 PERCENT tableID, 1 as extraOrderBy FROM tablename WHERE fk1=4 ORDER BY orderBy ASC
UNION ALL
SELECT TOP 100 PERCENT tableID, 2 as extraOrderBy FROM tablename WHERE fk2=4 ORDER BY orderBy ASC ) ORDER BY extraOrderBy ASC, orderBy ASC
I'm really just making this syntax up... 
No, you are 100% right.
I just need to add the extra column, and I am sorted. Thanks very much.. it was late in the day, after a late, then sleepless night, and more coffee than I should have had.
That solution is perfect.
:zippy:
glad to hear my muddled brain can still produce something useful once in a while
oh stop the self-deprecating dbm you genius!
heh ask my wife about my muddled brain, she'll tell you the grim truth