Need Ideas for SQL Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

todtown - 21 Jul 2008 20:56 GMT

I have a query that needs to return only if there are records that
match the criteria. My current query is something like this:

SELECT MAX(RECORD_DATE),RECORD_NAME
FROM TABLE_NAME
WHERE RECORD_DATE >= '7/21/2008' /*Today's Date*/
GROUP BY RECORD_NAME

The problem is that there can be thousands of records. The query can
often time out. And all I need to know is if there are any records. If
there is even one that's all I need to know.

Could somebody help me with this one?

tod

Reply to this Message

Hugo Kornelis - 21 Jul 2008 21:15 GMT

>I have a query that needs to return only if there are records that
>match the criteria. My current query is something like this:
[quoted text clipped - 9 lines]
>
>Could somebody help me with this one?

Hi tod,

If you only need to know if rows match the filter, then you don't need
to determine the maximum record_date to achieve that. Instead, use an
EXISTS query so that SQL Server knows it can stop after finding the
first match.

IF EXISTS
(SELECT *
FROM YourTable
WHERE YourDate >= '20080721' -- Use unambiguous date format
)
PRINT 'It exists!';
ELSE
PRINT 'None found..';

Signature

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Reply to this Message

Roy Harvey (SQL Server MVP) - 21 Jul 2008 23:04 GMT

>Hi tod,
>
[quoted text clipped - 11 lines]
>ELSE
> PRINT 'None found..';

Or if you would prefer the answer in a SELECT result instead of PRINT
commands:

SELECT CASE WHEN EXISTS
(SELECT * FROM YourTable
WHERE YourDate >= '20080721')
THEN 'Yes'
ELSE 'No'
END as TestResult

Of course instead of 'Yes' and 'No' and pair of values of compatible
type can be used, including 1 and 0.

Roy Harvey
Beacon Falls, CT

Reply to this Message

--CELKO-- - 21 Jul 2008 21:18 GMT

>> I have a query that needs to return only if there are records [sic: rows are not records] that match the criteria. And all I need to know is if there are any records [sic]. If here is even one that's all I need to know. <<

SELECT DISTINCT CURRENT_TIMESTAMP, 'Yes, we will have work!'
FROM Dummy -- any table
WHERE EXISTS
(SELECT *
FROM Foobar
WHERE record_date >= CURRENT_TIMESTAMP);

Your approach was to count the rows as if they were records in a
sequence. They are elements in a set; look at the set as a whole, not
as separate records. Use the right words and the proper mindset
follows.

Reply to this Message