Merge Two resultsets from a Stored Procedure

[Former Member]

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
)

[Breck_Carter]

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'

[VolkerBarth]

Though it's not stated, I would have expected an ORDER BY dt_joined DESC since the newest/latest records should be preferred...

[reimer_pods]

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

[VolkerBarth]

I guess this would not prefer the rows from tbl_class_a, as it should.

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...

[Former Member]

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

[Breck_Carter]

Could not execute statement.
Syntax error near 'UNION' on line 5
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1