Sorting UNION ALL Inputs Differently
You use a UNION ALL query to vertically join several inputs to make a unified row set. Note that UNION ALL doesn't eliminate duplicates, in contrast to UNION, which does. Suppose you partition the Sales table (in the Pubs database) into three tables, each containing a different year:
SELECT * INTO Sales1992 FROM Sales
WHERE YEAR(ord_date) = 1992
SELECT * INTO Sales1993 FROM Sales
WHERE YEAR(ord_date) = 1993
SELECT * INTO Sales1994 FROM Sales
WHERE YEAR(ord_date) = 1994
You can't sort the inputs separately on different columns because a UNION ALL operation results in a unified set of rows; instead, you can have only one ORDER BY operation for the entire set. For example, the following query isn't legal:
SELECT * FROM Sales1992 ORDER BY <qty>
UNION ALL
SELECT * FROM Sales1993 ORDER BY <ord_date>
UNION ALL
SELECT * FROM Sales1994 ORDER BY <title_id>
Ruban submitted the solution that Listing 1 shows, which lets you overcome this limitation. The sort0 column has a different value (i.e., 1, 2, or 3) for each input, which determines the first order for the output rows. For each group of values in the sort0 column, the query computes another column from the values to determine the group's secondary order. For example, for the first input, sort1 holds the qty column's values and all the other rows have NULL in that column. Similarly, for the second input, sort2 holds the ord_date column's values and all the other rows have NULL in that column, and so on.
Random Sort
You might think a request to sort output randomly is strange; nevertheless, it appears from time to time in public newsgroups. If you need the output rows in random order, Guerrero and Karaszi have provided some ideas about how to solve this problem. Obviously, you need a randomizing function. T-SQL supplies the RAND(int_seed) function, but you need to consider some important factors when you use it.
When you supply an argument to the RAND() function, the function doesn't produce a random value but rather a computation based on the supplied seed (i.e., for the same input value, the function always returns the same output). If you invoke the RAND() function with no input value, the system generates its own seed. Thus, you might think that if you sort your query by the RAND() function's result, you will get a random sort. To see what actually happens, create the following T1 table:
CREATE TABLE T1 (<int_key> INT IDENTITY)
INSERT T1 DEFAULT VALUES
INSERT T1 DEFAULT VALUES
INSERT T1 DEFAULT VALUES
INSERT T1 DEFAULT VALUES
INSERT T1 DEFAULT VALUES
Now, run the following query several times:
SELECT *, RAND() AS <rnd> FROM T1
ORDER BY <rnd>
Note that each time you run the query, SQL Server evaluates the RAND() function only once. Thus, all the rows will have the same value in the rnd column, making it inefficient for the task. Another option might be to use an integer key column as the seed:
SELECT *, RAND(<int_key>) AS <rnd> FROM T1
ORDER BY <rnd>
But keep in mind that the RAND() function always returns the same value for the same input. So if you issue this query several times consecutively, the output will remain the same unless the table changes in the meantime.
One way to solve this problem is to create a temporary table to hold only the table's keys and a float column to hold the random values. You can create a loop that iterates through all the key values, and for each key, invokes the RAND() function with no input, updating the float column with the random value generated. When the loop finishes executing, you join the original table to the temporary table and order the result by the random values' column. For example, to produce random output for the Authors table in the Pubs database, you can use the code that Listing 2 shows. I use the lock hints TABLOCK SERIALIZABLE to prevent modifications to the Authors table until the transaction finishes. (Dejan Sarka also presents an elegant solution to random sorting in "Tips from the SQL Server MVPs," http://www.sqlmag.com, InstantDoc ID 19842.)
Query Performance Counts
As NULLs, UNION ALL, and random sorting show, sorting issues aren't always trivial. When you get a complex sorting task, you first must solve the problem logically, providing the required output, but performance issues also figure into the mix. Computed columns and indexes can improve your queries' performance.
End of Article
Prev. page
1
[2]
next page -->
