Strange SQL Query

VectorX7 · #1 (**permalink**) 10-21-11, 10:22

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?

Lenny77 · #2 (**permalink**) 10-21-11, 11:05

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

Peter.Vanroose · #3 (**permalink**) 10-21-11, 17:28

Quote:

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

tonkuma · #4 (**permalink**) 10-22-11, 02:11

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)) ) )
;

tonkuma · #5 (**permalink**) 10-22-11, 14:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech