By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,287 Members | 1,287 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,287 IT Pros & Developers. It's quick & easy.

Db2 Union

P: 2
Been a while since I put a query together but I'm stuck getting my head around something. I want to run a query that using multiple wheres and returns two random examples of each. I can get it to work individually but I have over 200 of these to run and a combined report would be so much easier in case I need to re-run. Any help appreciated!

SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M2'
ORDER BY RAND_NO
FETCH FIRST 2 ROWS ONLY
UNION
SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M3'
ORDER BY RAND_NO
FETCH FIRST 2 ROWS ONLY
UNION
etc . . . .
Feb 13 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Been a while since I put a query together but I'm stuck getting my head around something. I want to run a query that using multiple wheres and returns two random examples of each. I can get it to work individually but I have over 200 of these to run and a combined report would be so much easier in case I need to re-run. Any help appreciated!

SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M2'
ORDER BY RAND_NO
FETCH FIRST 2 ROWS ONLY
UNION
SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M3'
ORDER BY RAND_NO
FETCH FIRST 2 ROWS ONLY
UNION
etc . . . .
Hi Dan

I'm not familar with DB2 SQL Syntax but if you are then we can look at this from an optimisation point of view. I'm going to ask someone else to look at it as well.

Mary
Feb 14 '07 #2

NeoPa
Expert Mod 15k+
P: 31,533
I honestly have great difficulty following the logic of what's been posted.
However, assuming a table called db2 I've put something together that might fit what you may be asking for :confused:
If not, it should at least be a base from which to build.
Expand|Select|Wrap|Line Numbers
  1. SELECT Product,Contract,Number
  2. FROM (SELECT TOP 2 Product,Contract,Number
  3.       FROM db2
  4.       WHERE Product='M2')
  5. UNION SELECT Product,Contract,Number
  6. FROM (SELECT TOP 2 Product,Contract,Number
  7.       FROM db2
  8.       WHERE Product='M3')
  9. UNION SELECT Product,Contract,Number
  10. FROM (SELECT TOP 2 Product,Contract,Number
  11.       FROM db2
  12.       WHERE Product='M4')
  13. ...
Feb 14 '07 #3

P: 2
I suppose I've been confusing everyone really.

To clarify.

I have a table with 5 million rows.
Each row has type, e.g. M1, M2, M3 etc although the naming is not so uniform
For testing I need to pull 2 random examples of each from the table and there are over 200 types to be tested. I can pull two samples of each type using my first query but no more. Repeating this two hundred times would take forever considering that it takes approx 5 mins to run a query on this table.

Hope this helps somewhat .. .

Dan
Feb 14 '07 #4

NeoPa
Expert Mod 15k+
P: 31,533
Dan,
That certainly clarifies things.
That's the good news :(
The method I posted earlier is the only one I can think of (atm at least) which even does this type of selection. I do appreciate your reservations about it mind you. There's no SQL statement that I know of to say 'Return rows in a balanced order, ensuring, on a particular field, that items don't repeat until all other items within that field have shown'.
Using this method, you could build up the SQL in code, then rely on the SQL optimiser to make it work efficiently. I'm pretty sure it won't mind you :(
I'll bear your question in mind if anything else springs to mind.
Feb 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.