Sql Help!!!

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. So far its just return 100 random examples from anywhere i.e. set in the fetch statement. Even if i could get it to return 1 of each I could work with that. Any help appreciated!

SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M2'
or PRODUCT = 'MT'
or PRODUCT = 'CM'
etc....
ORDER BY RAND_NO
FETCH FIRST 100 ROWS ONLY

keeffe2001

you could do this by merging a number of queries together and limiting each one to 2. Depending on which database you are using there is different key words to limit results (doing a search on google will clear it up)

SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'M2'
ORDER BY RAND_NO
LIMIT 0,2
union
SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'MT'
ORDER BY RAND_NO
LIMIT 0,2
union
SELECT PRODUCT, CONTRACT, NUMBER
FROM (PRODUCT, CONTRACT, NUMBER, RAND() FROM db2) Q
(PRODUCT, CONTRACT, NUMBER, RAND_NO)
Where PRODUCT = 'CM'
ORDER BY RAND_NO
LIMIT 0,2
etc

danindublin

Thanks

Seem to be a bit further along but its still looking for a fetch statment and doesnt like any variation of what was previously there! :mad: