Let's assume we have a table T with the following columns:
dc1 dc2 fc1 fc2 lc2 1 1 11 12 21 1 1 11 12 22 1 1 11 12 23 2 2 22 22 31 3 3 33 35 32 3 3 33 37 32
I want a SQL query that returns all the rows that satsfy the following criteria: dc1, dc2 has multiple distinct fc1, lc2 combinations (count > 1). For the above sample data, the SQL would return:
dc1 dc2 fc1 fc2 lc2 1 1 11 12 21 1 1 11 12 22 1 1 11 12 23
The solution is straightforward and utilizes COUNT DISTINCT:
select T.* from T inner join ( select dc1 , dc2 from T group by dc1 , dc2 having count( distinct fc1,lc2 ) > 1 ) as multiples on T.dc1 = multiples.dc1 and T.dc2 = multiples.dc2
Unfortunately, not every database system supports COUNT DISTINCT.
For those that don't, you can obtain the correct results, perhaps not as efficiently, with concatenation or some other expression to combine the two column values. For example, using Microsoft SQL Server syntax --
select T.* from T inner join ( select dc1 , dc2 from T group by dc1 , dc2 having count( distinct cast(fc1 as varchar) +cast(lc2 as varchar) ) > 1 ) as multiples on T.dc1 = multiples.dc1 and T.dc2 = multiples.dc2
Dig deeper on Oracle and SQL
Related Q&A from Rudy Limeback, SQL Consultant, r937.com
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback.continue reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clausecontinue reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.