Sql-server – Get rows having different values for a column based on the duplicate values of combination of other 3 columns

distinctsql servert-sql

I want to get only rows having a different values in a column(column name DEF) based on the duplicate rows having unique combination of other 3 columns.

Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output.

But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.

rows 5 and 6 are not to be listed since they are single row with different values.

+----------+-------+--------+--------+
| dept     | role1 |role2   |DEF     |
+----------+-------+--------+--------+
| a        | abc   | er     | 0      |
| a        | abc   | er     | 1      |
| b        | qwer  | ty     | 0      |
| b        | qwer  | ty     | 0      |
| c        | der   | ui     | 1      |
| d        | nerr  | io     | 0      |
+----------+-------+--------+--------+
output

+----------+------+------+------+
| dept     | role1|role2 |DEF   |
+----------+------+------+------+
| a        | abc  | er   |0     |
| a        | abc  | er   |1     |
+----------+------+------+------+

I tried using distinct with having but not able to check the values of column DEF to get desired result.

Can anyone help me on this?

Best Answer

Using standard SQL on most RDBMS, there are various ways.

Using a subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM data d
INNER JOIN (
    SELECT dept, role1, role2 
    FROM data
    GROUP BY dept, role1, role2
    HAVING COUNT(distinct DEF) > 1
) dup
    ON dup.dept = d.dept AND dup.role1 = d.role1 AND dup.role2 = d.role2
;

The subquery returns sets of dept/role1/role2 with more than 1 distinct DEF.

Using a correlated subquery:

SELECT d.dept, d.role1, d.role2, DEF
FROM @data d
WHERE EXISTS (
    SELECT 1 
    FROM @data 
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
);

The subquery return 0 to n rows. If at least one row exists, the row from the main table is returned.

Using `CROSS APPLY`:

SELECT d.dept, d.role1, d.role2, d.DEF
FROM @data d
CROSS APPLY (
    SELECT n=1 
    FROM @data 
    WHERE dept = d.dept AND role1 = d.role1 AND role2 = d.role2 AND DEF <> d.DEF
) ca
;

CROSS APPLY works with Oracle or SQL Server.

Output:

dept    role1   role2   DEF
a       abc     er      0
a       abc     er      1

Related Solutions

Sql-server – How to calculate values based on the previous row after skipping the first 12 rows

So this isn't a great answer, this is kind of a starting answer for somebody else to take on and refine this better. But I'll make a stab at it.

First I have a question: Are you trying to retain this in a view? I don't think you can for what you're wanting to do, it's kinda complicated, so let's examine the operations that you need to do to actually do what you want.

You stated that you want the first 12 rows to be static every time, and they should always have their last column set as NULL, and the others should retain their value. So that's a business rule that we need to encode in SQL. But before we encode this as a rule, let's ask if there's a way to ENSURE that those 12 rows are the RIGHT rows every time. If we can make that assumption, then we can do this as part of the next step.

You're next requirement is to do a calculation on each row with the previous row. Since the first 12 rows are static (and I presume not calculated) then we don't have to ask "what about the first row". So the easiest way to do calculations on the previous row is to assign a rownum to each row, then use the rownum ID in a comparison. This meshes with the previous requirement.

So we should start by doing our select and assigning a rownum as well, like this:

SELECT     
    ROW_NUMBER() OVER (ORDER BY in.I_Date) AS rownum,
    in.I_Date  ,--Date
    in.I_O_P   ,--Money
    in.I_O_H   ,--Money
    in.I_O_L   ,--Money
    in.I_C_O   ,--Money 
    c.AMPS12_C  --Money
    CAST(0.0 AS Money) AS C12WR
FROM
    dbo.IC_Raw_In in
INNER JOIN 
    dbo.AMPS12_C c ON in.I_Serial = c.i_serial

But for the way I would do this, I would funnel these values into a temp table, and then use that to work out what I need. That way you can just refer to the columns in subsequent calls, like this:

UPDATE t 
SET C12WR = NULL
FROM temptable t
WHERE t.rownum < 12 -- see how we set the values = null here?

UPDATE t 
SET C12WR = 510.3958
FROM temptable t
WHERE t.rownum = 12 -- see how we set the value to something static? 
                    -- If this were a stored procedure we could use a value passed in here

and then we continue with:

UPDATE t 
SET C12WR = ( ( t2.C12WR * 11.0 ) + t.I_C_O ) / 12.0
FROM temptable t
INNER JOIN temptable t2 ON t.rownum = (t2.rownum - 1) -- this let's us get the previous row
WHERE t.rownum > 12

Using this logic: After the 13th row, the C12WR column = (prevrow.C12WR * 11 + currow.I_C_O Column) / 12

And then you would just return the values that you wanted from the temptable.

Notice: the things I left off. I did not define the temp table, I did not get rid of the temptable. I did not use appropriate syntax for the temptable addressing. I did not validate anything. I presumed that this was going to be used in a stored procedure. I did not illustrate how to use the static value as a stored procedure passed parameter.

Hope this helps. Hope someone else helps make this a better answer ;)

Sql-server – Select rows with same id but null and some other value in another column for that id

You should be able to use conditional aggregation to get the username with both a value in col2 as well as null.

I'd suggest using a HAVING clause with the conditions. The query would be similar to:

select username
from yourtable
group by username
having sum(case when col2 is not null then 1 else 0 end) = 1
  and sum(case when col2 is null then 1 else 0 end) = 1

See SQL Fiddle with Demo. This query groups your data by each username and then uses conditional logic to check if col2 meets both conditions you want - where col2 is not null and col2 is null.

You can then use this in a subquery, etc to get the username and col2 values:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) = 1
    and sum(case when col2 is null then 1 else 0 end) = 1
) d
  on t.username = d.username

See SQL Fiddle with Demo.

If you have more than one col2 row with both null and another value, then you just need to alter the HAVING clause slightly:

select 
  t.username, 
  t.col2
from yourtable t
inner join
(
  select username
  from yourtable
  group by username
  having sum(case when col2 is not null then 1 else 0 end) >= 1
    and sum(case when col2 is null then 1 else 0 end) >= 1
) d
  on t.username = d.username;

See SQL Fiddle with Demo