>I have a SELECT statement that has a few unions. I dont use ORDER BY because I want to have selected records in the order I am selecting them in the Unions. But that's not happening. Any help, please?
>
I guess, it is because UNION returns DISTINCT records, to eliminate duplicates the result is sorted first. You can use UNION ALL instead if you are sure that DISTINCT behavior is not required or all records are unique.
Here is a small code that illustrates how the order is affected by UNION / UNION ALL:
CREATE CURSOR foo1 (f1 I)
INSERT INTO foo1 values(2)
INSERT INTO foo1 values(4)
INSERT INTO foo1 values(6)
CREATE CURSOR foo2 (f1 I)
INSERT INTO foo2 values(1)
INSERT INTO foo2 values(3)
INSERT INTO foo2 values(5)
SELECT * from foo1 UNION SELECT * from foo2
SELECT * from foo1 UNION ALL SELECT * from foo2
Thanks,
Aleksey Tsingauz.