CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
tonyf
tonyf

Reputation: 35557

How to check Oracle column values are all the same for a specific ID?

I am trying to figure out the best way to determine, for a specific ID within an Oracle 11g table that has 5 columns and say 100 rows against this ID, if all the column values are the same for these five columns.

For example:

Table Name: TABLE_DATA

Columns:

TD_ID   ID      COL1    COL2    COL3    COL4    COL5
-----------------------------------------------------------------------
1       1       1       0       3       2       0
2       1       1       0       3       2       0
3       1       1       0       3       2       0
4       1       1       0       3       2       0
5       1       1       0       3       2       0
6       1       1       0       3       2       0

So based on the above example which is just showing 6 rows for now against the ID:1, I want to check that for all COL1, COL2, COL3, COL4 and COL5 values where ID = 1, tell me if all the values are the same from the very first row right down to the last – if so, then return ‘Y’ else return ‘N’.

Given the above example, the result would be ‘Y’ but for instance, if TD_ID = 5 and COL3 = 4 then the result would be ‘N’, as all the column values are not the same, i.e.:

TD_ID   ID      COL1    COL2    COL3    COL4    COL5
-----------------------------------------------------------------------
1       1       1       0       3       2       0
2       1       1       0       3       2       0
3       1       1       0       3       2       0
4       1       1       0       3       2       0
5       1       1       0       4       2       0
6       1       1       0       3       2       0

I’m just not sure what the fastest approach to determine this is, as the table I am looking at may have more than 2000 rows within the table for a specific ID.

Upvotes: 1

Views: 14753

Answers (3)

AndrewMcCoist
AndrewMcCoist

Reputation: 639

Here's a little query, you might wanna try out (eventually, you just could try figuring out a better MINUS statement for you):

SELECT
  CASE 
    WHEN (  -- select count of records from a subquery
            SELECT 
              COUNT(1) 
            FROM
            ( -- select all rows where id = 1
              SELECT
                td.col1
                ,td.col2
                ,td.col3
                ,td.col4
                ,td.col5
              FROM
                table_data td
              WHERE
                td.id = 1
              MINUS -- substract the first row of the table with id = 1
              SELECT
                td.col1
                ,td.col2
                ,td.col3
                ,td.col4
                ,td.col5
              FROM
                table_data td
              WHERE
                td.id = 1
                AND ROWNUM = 1
            )
        ) = 0 -- check if subquery's count equals 0
        AND EXISTS (  -- and exists at least 1 row in the table with id = 1
          SELECT
            1
          FROM
            table_data td
          WHERE
            td.id = 1
            AND ROWNUM = 1
        ) THEN 'Y' 
    ELSE 'N' 
  END AS equal
FROM
  dual

Upvotes: 0

Massimiliano Piccinini
Massimiliano Piccinini

Reputation: 41

You may also try this :

Select ID 
, case when count(distinct COL1 || COL2 || COL3 || COL4 || COL5) > 1 
  then 'N'
  else 'Y' end RESULT
From TABLE_DATA
Group by id;

In this way you group by id and counts how many distinct combination are there. If only 1 , so all the rows have the same set of values, otherwise it don't.

Upvotes: 3

PM 77-1
PM 77-1

Reputation: 13334

See if the following is fast enough for you:

SELECT ID, CASE WHEN COUNT(*) > 1 THEN 'No' ELSE 'Yes' END As "Result"
FROM (SELECT DISTINCT ID, COL1, COL2, COL3, COL4, COL5
      FROM Table_Data) dist
GROUP BY ID

Upvotes: 2

Related Questions