I have two IDENTICAL tables tbl_class_a and tbl_class_b. I want to select the top five from table A ordering by date, if there are not enough (usually the case as the table is emptied daily) i take the balance from table B. Was able to successfully extract two resultsets but the client application only want one resultset. How can i merge the 2 resultsets or query both tables and extract the top five according to the dates (reason why table A has precedence is that it has the latest records which are transferred to table B at end of day?) CREATE TABLE tbl_class_a ( u_name varchar(20), dt_joined date ); CREATE TABLE tbl_class_b ( u_name varchar(20), dt_joined date ) |
It feels like there are too many TOP 5's in this, but maybe not :)... Also, it's not clear what the constraints are; e.g., can different rows in the same or different table have the same dates? It is ALWAYS better to post real code, rather than (over?) simplified table definitions. CREATE TABLE tbl_class_a ( u_name varchar(20), dt_joined date ); CREATE TABLE tbl_class_b ( u_name varchar(20), dt_joined date ); INSERT tbl_class_a VALUES ( 'a1', CURRENT DATE ); INSERT tbl_class_a VALUES ( 'a2', CURRENT DATE + 1 ); INSERT tbl_class_a VALUES ( 'a3', CURRENT DATE + 2 ); INSERT tbl_class_b VALUES ( 'b1', CURRENT DATE ); INSERT tbl_class_b VALUES ( 'b2', CURRENT DATE + 1 ); INSERT tbl_class_b VALUES ( 'b3', CURRENT DATE + 2 ); INSERT tbl_class_b VALUES ( 'b4', CURRENT DATE + 3 ); INSERT tbl_class_b VALUES ( 'b5', CURRENT DATE + 4 ); INSERT tbl_class_b VALUES ( 'b6', CURRENT DATE + 5 ); COMMIT; SELECT TOP 5 * FROM ( SELECT TOP 5 u_name, dt_joined FROM ( SELECT * FROM ( SELECT TOP 5 'a' AS source, * FROM tbl_class_a ORDER BY dt_joined ) AS a UNION ALL SELECT * FROM ( SELECT TOP 5 'b' AS source, * FROM tbl_class_b ORDER BY dt_joined ) AS b ) AS ab ORDER BY source, dt_joined ) AS top5ab ORDER BY dt_joined, u_name; u_name,dt_joined 'a1','2011-08-01' 'b1','2011-08-01' 'a2','2011-08-02' 'b2','2011-08-02' 'a3','2011-08-03' Though it's not stated, I would have expected an ORDER BY dt_joined DESC since the newest/latest records should be preferred...
(04 Aug '11, 15:41)
Volker Barth
|
try simple query like that: select TOP 5 dt_joined, u_name from ( SELECT TOP 5 dt_joined, u_name from tbl_a order by dt_joined UNION ALL SELECT TOP 5 dt_joined, u_name from tbl_b order by dt_joined ) order by dt_joined Could not execute statement. Syntax error near 'UNION' on line 5 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1
(01 Aug '11, 07:10)
Breck Carter
Comment Text Removed
|
What about this? select TOP 5 dt_joined, u_name from ( SELECT TOP 5 dt_joined, u_name from tbl_class_a UNION ALL SELECT TOP 5 dt_joined, u_name from tbl_class_b ) as bt order by dt_joined asc
I have to correct myself: As tbl_class_a is said to hold the newest/latest records, there's an implicit order between both tables, so this should work. As commented above, however, I would expect an ORDER BY ... DESC...
(04 Aug '11, 15:38)
Volker Barth
|