Check if column value in query is the same in every row

Question

I'm looking to be able to see if the value for a column is the same throughout a collection of rows, based on the value of the first row in SQL.

Currently I'm using the following query based on the answer to this similar question.

SELECT CASE
    WHEN NOT EXISTS(
        SELECT * FROM dbo.Table
        WHERE colA = 'valueA'
        AND colD <> (
            SELECT TOP 1 colD
            FROM dbo.Table
            WHERE LTRIM(colA) = 'valueA'
            AND colB = 'valueB'
            AND (
                colC = 'valueC1'
                OR colC = 'valueC2'
                ... OR colC = 'valueCn'
            )
        )
        AND colB = 'valueB'
        AND (
            colC = 'valueC1'
            OR colC = 'valueC2'
            ... OR colC = 'valueCn'
        )
    ) THEN 'Y'
    ELSE 'N'
END AS my_result

As far as I've been able to test so far, this works, but I've only used a few test cases. It's also not very scalable, which could be a problem since colC could potentially have hundreds of values.

Is there another command or method to handle this better within SQL, or perhaps would it be better to pull the inner SELECT statement into it's own query, and then take the result and add it to the outer SELECT statement in another resulting query? Or, am I going about this all wrong to begin with?

This is in SQL Server 2008 R2 SP3, which I've added as a tag. I'm looking for a statement that will preferably return some sort of boolean value that I can evaluate on, similar to how this statement currently returns either Y or N. However, if there's a better solution that requires a different output, I can most likely adjust to deal with that. I'm more concerned in having a good, scalable solution.

Used_By_Already has a good start, however I'm not going to know what the data is ahead of time, so I need a purely dynamic solution.

The planned use case for this is to feed in a single value for colA and colB, and n values for colC, the structure always being colA AND colB AND (colC1 OR colC2 OR...colCn). These values are being used to retrieve the first datetime from colD that is then checked against every value in colD, again filtering by the criteria in the first query. I won't know the value of the datetime in colD until the first query is done.

The only information I have ahead of time before going into this query, is colA, colB, and each value of colC. colC will always have at least one value, but could potentially have hundreds of values. I'm needing to check if every instance filtered by my WHERE statement was recorded in the system at the same datetime object, or if they were different. I don't know what that datetime is, and in all honesty, it's irrelevant when it happened, so long as each time it happened is the same.

SQL Fiddle

CREATE TABLE Example
    ([colA] nvarchar(20), [colB] nchar(1), [colC] smallint, [colD] datetime)
;

INSERT INTO Example
    ([colA], [colB], [colC], [colD])
VALUES
    ('123610', 'S', '1', '2017-06-17 11:53:52'),
    ('123610', 'S', '2', '2017-06-17 11:53:52'),
    ('123610', 'R', '3', '2017-06-17 11:53:52'),
    ('123610', 'S', '4', '2017-06-17 11:53:52'),
    ('123611', 'S', '1', '2017-06-17 11:53:52'),
    ('123610', 'S', '5', '2017-06-14 11:53:52'),
    ('123610', 'S', '3', '2017-06-17 11:53:52'),
    ('123610', 'S', '7', '2017-06-15 11:53:52'),
    ('123610', 'S', '8', '2017-06-17 11:53:52'),
    ('123610', 'S', '9', '2017-06-17 11:53:52')
;

Query 1

SELECT CASE
    WHEN NOT EXISTS(
        SELECT * FROM Example
        WHERE colA = '123610'
        AND colD <> (
            SELECT TOP 1 colD
            FROM Example
            WHERE colA = '123610'
            AND colB = 'S'
            AND (
                colC = '1'
                OR colC = '2'
                OR colC = '7'
                OR colC = '5'
            )
        )
        AND colB = 'S'
        AND (
            colC = '1'
            OR colC = '2'
            OR colC = '7'
            OR colC = '5'
        )
    ) THEN 'Y'
    ELSE 'N'
END AS my_result

Results

|my_result|
|---------|
|    N    |

Query 2

SELECT CASE
    WHEN NOT EXISTS(
        SELECT * FROM Example
        WHERE colA = '123610'
        AND colD <> (
            SELECT TOP 1 colD
            FROM Example
            WHERE colA = '123610'
            AND colB = 'S'
            AND (
                colC = '1'
                OR colC = '2'
                OR colC = '3'
                OR colC = '8'
            )
        )
        AND colB = 'S'
        AND (
            colC = '1'
            OR colC = '2'
            OR colC = '3'
            OR colC = '8'
        )
    ) THEN 'Y'
    ELSE 'N'
END AS my_result

Results

|my_result|
|---------|
|    Y    |

Query 3

SELECT CASE
    WHEN NOT EXISTS(
        SELECT * FROM Example
        WHERE colA = '123610'
        AND colD <> (
            SELECT TOP 1 colD
            FROM Example
            WHERE colA = '123610'
            AND colB = 'S'
            AND (
                colC = '1'
                OR colC = '4'
                OR colC = '3'
                OR colC = '8'
            )
        )
        AND colB = 'S'
        AND (
            colC = '1'
            OR colC = '4'
            OR colC = '3'
            OR colC = '8'
        )
    ) THEN 'Y'
    ELSE 'N'
END AS my_result

Results

|my_result|
|---------|
|    N    |

What version of SQL-Server? Also, an example of current output and desired output would be helpful. — BJones, Oct 10 '17 at 17:36
Can you show an example of a table with columns A through D and values in each column which you expect to return Y and another table with rows you expect to return N? — Beth, Oct 10 '17 at 18:18
Example of sample data and expected results will help to answer to your question. — Artashes Khachatryan, Oct 10 '17 at 20:04

score 0 · Answer 1 · answered Oct 10 '17 at 21:36

Borrowing sample data from that similar question I setup the following:

SQL Fiddle

CREATE TABLE Table1
    ([ID] varchar(4), [Status] int)
;

INSERT INTO Table1
    ([ID], [Status])
VALUES
    ('y123', 2),
    ('y432', 2),
    ('y531', 2),
    ('y123', 2),
    ('n123', 1),
    ('n432', 3),
    ('n531', 2),
    ('n123', 2)
;

Query 1:

select 
  coalesce(
            (select 'Y' as yn
              from (
                select count(distinct [Status]) yn
                from table1
                where id like 'y%'                 -- data for yes
                having count(distinct [Status]) = 1
                ) as yn)
          ,'N')

Results:

|   |
|---|
| Y |

Query 2:

select 
  coalesce(
            (select 'Y' as yn
              from (
                select count(distinct [Status]) yn
                from table1
                where id like 'n%'                 -- data for no
                having count(distinct [Status]) = 1
                ) as yn)
          ,'N')

Results:

|   |
|---|
| N |

The main problem with this is that I won't know what the value is ahead of time, outside of it being a datetime. — Malil, Oct 12 '17 at 04:33
So you have a variable that needs to be compared against a set of rows, that variable is datetime. Where is the problem that you perceive? What rows are you comparing to? What will you be using this for? (eg. a trigger, a procedure? ...) don't just respond here. If it helps everyone edit the question. — Paul Maxwell, Oct 12 '17 at 04:44

Check if column value in query is the same in every row

1 Answers1