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
)

asked 01 Aug '11, 05:10

Takudzwa's gravatar image

Takudzwa
818813
accept rate: 0%

edited 01 Aug '11, 06:55

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050


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'
permanent link

answered 01 Aug '11, 07:09

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 01 Aug '11, 07:20

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
permanent link

answered 01 Aug '11, 05:30

AlexeyK77's gravatar image

AlexeyK77
70761224
accept rate: 8%

edited 01 Aug '11, 05:31

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
permanent link

answered 04 Aug '11, 08:38

Reimer%20Pods's gravatar image

Reimer Pods
4.5k384891
accept rate: 11%

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

(04 Aug '11, 15:38) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125

question asked: 01 Aug '11, 05:10

question was seen: 3,172 times

last updated: 04 Aug '11, 15:43