Determine if any values satisfy a condition

Question

How do I write an effiecient query to determine if 1 or more values are 10+ in a column. I am aware that I can count the values but this will scan all the records. Here is what I have so far:

SELECT COUNT(*) FROM MyTable WHERE [state] = 12 AND age > 110

I want this query to stop when it find the first person over 110 not scan the entire table. Is this possible?

@scsimon I just want to know if state 12 has any people over 110. I don't need the count. — Luke101, Dec 05 '16 at 02:39
Then just select 1 or select 'true' with the same where clause. This will save you the aggregation expense. You may want to pair this with EXISTS — S3S, Dec 05 '16 at 02:40
If you are using this with logical checks then you can use something like IF (SELECT TOP 1 state FROM yourTable WHERE ....) IS NOT NULL BEGIN ... code... END ELSE BEGIN ... code .... END — S3S, Dec 05 '16 at 02:45

score 3 · Accepted Answer · answered Dec 05 '16 at 02:47

You can use a subquery to return 1 or no row using this query:

SELECT TOP 1 1 as row_exists
FROM MyTable
WHERE [state] = 12 AND age > 110;

You can use a subquery to return 1 or NULL using this as a subquery:

SELECT (SELECT TOP 1 1 FROM MyTable WHERE [state] = 12 AND age > 110
       )  as row_exists;

You can put this into T-SQL using:

IF (EXISTS (SELECT 1 FROM MyTable WHERE [state] = 12 AND age > 110))
BEGIN
    . . .
END;

TOP is not needed in an EXISTS subquery.

score 2 · Answer 2 · answered Dec 05 '16 at 02:46

2

So you wish to have the scalar Boolean result? The exists will quite once any row matches the condition

DECLARE @Result bit = 
    (SELECT CASE WHEN EXISTS(SELECT * FROM MyTable WHERE [state] = 12 AND age > 110) THEN 1 ELSE 0 END)

answered Dec 05 '16 at 02:46

Eric

5,675
16
24

1

btw, using `SELECT *` is just same as `SELECT 1` in `EXISTS` http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists – Eric Dec 05 '16 at 02:54

score 0 · Answer 3 · answered Dec 05 '16 at 03:02

I am not sure wether the it is helpful for you, but you can try to test:

For example you can want to determine the result set row count is 100. you can use top 100 base your statement. if the orignal result lines is more than 100, then @@ROWCOUNT will be true.

SELECT  TOP 100 FROM MyTable WHERE [state] = 12 AND age > 110
IF @@ROWCOUNT=100
     PRINT 'True'
ELSE 
     PRINT 'Flase'

Determine if any values satisfy a condition

3 Answers3