1. Registered User
Join Date
Oct 2011
Posts
1

I have a table that looks like this:
Code:
```A | B | C | D
--------------
0 | 1 | 4 | 6
0 | 3 | 2 | 3
1 | 1 | 4 | 6
0 | 3 | 3 | 4```
If a record has the same values in column B, C and D as another record, I only want to return the one in which column A has a value of 1.
I also want to return all records that have distinct values of B, C and D. So, in this case, I'd want to return all but the first row.
How do I write a query that actually accomplishes that, though?

2. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
something like this ?

Code:
```select *
from table1
where a = 1 and (b = c) and ( d <> c)

union all
select * from
(select a, b, c, d
from table1
where (b <> c) and ( c <> d)
fetch first row only ) aa```

3. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by VectorX7
If a record has the same values in column B, C and D as another record, I only want to return the one in which column A has a value of 1.
I also want to return all records that have distinct values of B, C and D.
If I understand this correctly, you want to group by (B,C,D), then show all groups of one line, and show the "A=1" line of all groups of more than 1 line, if present, correct?
In that case I would write:
Code:
```SELECT max(A),B,C,D
FROM my_table
GROUP BY B,C,D
HAVING count(*) = 1
UNION ALL
SELECT 1,B,C,D
FROM my_table
HAVING count(*) > 1 AND count(CASE A WHEN 1 THEN 1 END) > 0```
If there is a guarantee that all groups of more than one row have a row with A=1, and moreover all values of A are nonnegative, this would simplify to
Code:
```SELECT abs(min(CASE A WHEN 1 THEN -1 ELSE A END)),B,C,D
FROM my_table
GROUP BY B,C,D```

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
I think both conditions can be put in one HAVING clause.

Example 1:
Code:
```SELECT CASE COUNT(*)
WHEN 1 THEN
MAX(a)
ELSE 1
END  AS a
, b , c , d
FROM  a_table
GROUP BY
b , c , d
HAVING
COUNT(*) = 1
OR  MAX( CASE a WHEN 1 THEN 1 ELSE 0 END ) = 1
;```

Example 2:
Code:
```SELECT CASE COUNT(*)
WHEN 1 THEN
MAX(a)
ELSE 1
END  AS a
, b , c , d
FROM  a_table
GROUP BY
b , c , d
HAVING
1 IN ( COUNT(*) , MAX( INT(COS(a - 1)) ) )
;```

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
The CASE expression for column A in Example 1 and 2 can be replaced by another expression without using CASE expression.
Because, COUNT(*) is always positive integer.

Example 3:
Code:
```SELECT 1 / COUNT(*) * MAX(a - 1) + 1 AS a
, b , c , d
FROM  a_table
GROUP BY
b , c , d
HAVING
1 IN ( COUNT(*) , MAX( INT(COS(a - 1)) ) )
;```
Note: consider order of operators and result of integer division.
1) order of operators
1 / COUNT(*) * MAX(a - 1) + 1
is equivalent to
( ( 1 / COUNT(*) ) * MAX(a - 1) ) + 1

2) result of integer division
If COUNT(*) = 1, then ( 1 / COUNT(*) ) = 1
If COUNT(*) > 1, then ( 1 / COUNT(*) ) = 0
Last edited by tonkuma; 10-22-11 at 15:38. Reason: Add Note.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•