In this query I want the recordset to be empty if the count is more than ten. Other wise I want to know who many the count is.

SELECT pre_28_reg, count(*) AS count
FROM Meet_reg
WHERE pre_28_reg =2 AND count < 10
GROUP BY pre_28_reg

Nick

This should work:

Code:

SELECT pre_28_reg, count(*) AS count
FROM Meet_reg
WHERE pre_28_reg = 2 
GROUP BY pre_28_reg
having count(*) <= 10

I can't see why you are selecting pre_28_reg as you know the value will be 2 but I left it in. I changed the < 10 to be <= 10 as in your description.

Mike

Tanks Mike, it works great. I went for a long walk and realized I need to use have or max. Anything that looks simple isn't!

nick

r u can use derived query
but prefer mike_bike_kite query than this one............

select * from (SELECT pre_28_reg, count(*) AS count
FROM Meet_reg
WHERE pre_28_reg =2
GROUP BY pre_28_reg)s where count < 10

Interesting, so the alias for the derived doesn't need an AS?..thanks!

Nick

best practice is always to code AS, even though it is optional

(oracle developers will argue the opposite, since oracle, for some inexplicable reason, doesn't support AS)

here's a good example...

how many columns does this query return?coding style matters!!!