sql query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SQL-Asheq - 13 May 2008 10:43 GMT

I want to find missing data from a table. For example there are 1,2,5,7 in a
table. Here max is 7 and min is 1. Now missing data within max and min are
3,4,6. So how can find missing data.

Reply to this Message

Paul G. Tobey [eMVP] - 13 May 2008 16:13 GMT

Seems like you'd enumerate all of the rows, keep a bit map where each bit
corresponds to a single value being present and set to 1 those bits that you
find. Once you've done that, all bits still set to zero are those which are
missing. Why does this sound like a homework assignment?

Paul T.

>I want to find missing data from a table. For example there are 1,2,5,7 in
>a
> table. Here max is 7 and min is 1. Now missing data within max and min are
> 3,4,6. So how can find missing data.

Reply to this Message

Jin - 13 May 2008 19:06 GMT

On May 13, 4:43 am, SQL-Asheq <SQL-As...@discussions.microsoft.com>
wrote:

> I want to find missing data from a table. For example there are 1,2,5,7 in a
> table. Here max is 7 and min is 1. Now missing data within max and min are
> 3,4,6. So how can find missing data.

Strange question, but I'll bite.

Create another table containing all the valid values (1 - 7).
Then perform a query using NOT EXISTS() as in:

SELECT * FROM ValidValues
WHERE NOT EXISTS (SELECT 1 FROM ActualValues WHERE ActualValues.Value
= ValidValues.Value)

Reply to this Message