Get top n records from each of 5 tables-CodePudding

In SQL Server 2016, I have 5 large tables. What's the best way to select the top 1000 records from each of the 5 tables in one query?

This for example returns too much data.

Select * 
from table1 
cross apply table2
cross apply table3 
cross apply table4 
cross apply table5 with(nolock)

Also, I was looking to get every column from each of the 5 tables..but just the first 1000 rows from each table.

CodePudding user response：

Use UNION ALL instead of CROSS APPLY.

Note: If you have just one query, you have just one result set. A result set must have a well-defined list of columns. Thus, you must tell SQL Server which of your columns in those different tables are "equal", by putting them in the same position.

Example:

SELECT TOP 1000 a, b, c FROM table1 ORDER BY d
UNION ALL
SELECT TOP 1000 x, y, z FROM table2 ORDER BY w
UNION ALL
...

In this case, the data types of a and x must be compatible, same for b and y, etc.

The column names from the very first SELECT statement (a, b and c in this example) will be used as the column names of the result set.

CodePudding user response：

If you don't care about them being the same result set, just one query that returns all of them, you can just make 5 separate select statements in 1 query:

SELECT TOP 1000 * FROM table1
SELECT TOP 1000 * FROM table2
SELECT TOP 1000 * FROM table3
SELECT TOP 1000 * FROM table4
SELECT TOP 1000 * FROM table5

If for some reason you need to have them in a single result set, including a row number column then joining them on the row number might work:

SELECT *
FROM (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY [orderCol]) AS RN, * FROM table1) AS T1
JOIN (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY [orderCol]) AS RN, * FROM table2) AS T2 ON T1.RN = T2.RN
JOIN (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY [orderCol]) AS RN, * FROM table3) AS T3 ON T1.RN = T3.RN
JOIN (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY [orderCol]) AS RN, * FROM table4) AS T4 ON T1.RN = T4.RN
JOIN (SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY [orderCol]) AS RN, * FROM table5) AS T5 ON T1.RN = T5.RN