Db2 Union

[danindublin]

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

[MMcCarthy]

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

[NeoPa]

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.

SELECT Product,Contract,Number
FROM (SELECT TOP 2 Product,Contract,Number
      FROM db2
      WHERE Product='M2')
UNION SELECT Product,Contract,Number
FROM (SELECT TOP 2 Product,Contract,Number
      FROM db2
      WHERE Product='M3')
UNION SELECT Product,Contract,Number
FROM (SELECT TOP 2 Product,Contract,Number
      FROM db2
      WHERE Product='M4')
...

[danindublin]

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

[NeoPa]

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.