CODEWITHSUNDEEP
sqlsql-server
I Love Stackoverflow
I Love Stackoverflow

Reputation: 6868

SQL table filter on multiple columns

I have a table which is having a ID values of other tables. What I need to do is based on a set of column, filter should be assigned and including that if any unique row found then it should also get added to result.

My SQL table is as below:

A   B   C   D   E
1   1   1   1   0
1   1   1   1   2
1   2   1   1   1
1   2   1   1   0
2   1   1   1   3
2   1   1   1   4
2   1   2   2   1
3   1   1   2   0
3   1   1   2   1

Here, Columns are: A, B, C, D, E.

Filters needs to be assigned are as below:

  1. Value of column A,B,C,D will be same.
  2. If value of column A will be same but values of B,C,D will be different it should be added in the result.
  3. For each row in the result value of column E will be minimum

As per above filters, query's out put should be as below:

A   B   C   D   E
1   1   1   1   0
1   2   1   1   0
2   1   1   1   3
2   1   2   2   1
3   1   1   2   0

What I have come up till now is:

SELECT t1.*
FROM TestTable t1, 
     TestTable t2
WHERE   (t1.A = t2.A
    AND t1.B = t2.B
    AND t1.C = t2.C
    AND t1.D = t2.D
    AND t1.E < t2.E)

But in this query, I'm not getting the unique row which is having same value for column A but different values for columns B,C,D.

Upvotes: 0

Views: 1910

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

From what I can tell, you want:

select A, B, C, D, MIN(E)
from testtable t
group by A, B, C, D;

At the very least, this produces the output that you have specified.

Upvotes: 3

Related Questions