SQL Query on dependency of a column

Question

I have to write a query for a table where I have two columns lets say id1 and id2. In this query i have to ensure that id2 can have atmost three values for a single id1. I have tried using this query:

select id1,id2
from table
group by id1,id2
having count(id2)<=3

I am not getting the result through this query..any modifications?

Did you mean at most or at least? Your query is getting _at least_ — CoolBeans, Jun 17 '11 at 05:57

score 1 · Accepted Answer · edited Jun 17 '11 at 07:18

If (id1, id2) pairs aren't unique, then maybe like this:

SELECT t.id1, t.id2
FROM atable t
  INNER JOIN (
    SELECT id1
    FROM atable
    GROUP BY id1
    HAVING COUNT(DISTINCT id2) <= 3
  ) g ON t.id1 = g.id1
GROUP BY t.id1, t.id2

But if the pairs are unique, then the outer query doesn't need GROUP BY and the inner query doesn't need DISTINCT:

SELECT t.id1, t.id2
FROM atable t
  INNER JOIN (
    SELECT id1
    FROM atable
    GROUP BY id1
    HAVING COUNT(id2) <= 3
  ) g ON t.id1 = g.id1

score 0 · Answer 2 · answered Jun 17 '11 at 05:59

0

Your question is hard to understand... Maybe this?

select id1, id2 from table where (select count(id2) from table where id1 = id1) <= 3

answered Jun 17 '11 at 05:59

daniel.herken

1,095
7
19

score 0 · Answer 3 · answered Jun 17 '11 at 06:23

0

Please try this....

select a.id1,a.id2
from
table_name a,(select count(1),id1 from table_name a group by id1 having count(*)<=3) b
where a.id1=b.id1
order by id1

answered Jun 17 '11 at 06:23

Navin

299
4
6
15

score 0 · Answer 4 · edited May 23 '17 at 11:48

It is more typical to test for rows that fail the business rule e.g.

SELECT DISTINCT *
  FROM Table1
 WHERE EXISTS (
               SELECT T1.id1
                 FROM Table1 AS T1
                WHERE T1.id1 = Table1.id1
                GROUP 
                   BY T1.id1
               HAVING COUNT(*) > 3
              );

The idea is that once you have decided which rows violate the business rule and have you have scrubbed the bad data, you want to put in place a constraint to ensure the rule is not violated in the future e.g. (notice the same query construct):

ALTER TABLE Table1 ADD
   CONSTRAINT atmost_three_id2_values_for_a_single_id1
      CHECK (NOT EXISTS (
                         SELECT T1.id1
                           FROM Table1 AS T1
                          WHERE T1.id1 = Table1.id1
                          GROUP 
                             BY T1.id1
                         HAVING COUNT(*) > 3
                        ));

Sadly, your SQL product of choice probably does not support subqueries in CHECK constraints. You can 'design away' the need for a table-level constraint by adding a sequence number column, allowing you to use commonly-found UNIQUE and column-level CHECK constraints e.g.

CREATE TABLE Table1
(
 id1 INTEGER NOT NULL, 
 id2 INTEGER NOT NULL, 
 UNIQUE (id1, id2), 
 seq INTEGER DEFAULT 1 NOT NULL
    CHECK (seq BETWEEN 1 AND 3), 
 UNIQUE (id1, seq)
);

SQL Query on dependency of a column

4 Answers4