SQL command for distinct values in one column and multiple same values in another

Question

My table have the following kind of entries:

Serial Number | Cycle Number

55                33

56                27

55                34

55                33

57                5

57                6

56                28

57                6

I would like to return distinct Serial Numbers that have multiple Cycle Numbers with the same value

Serial Number |  Cycle Number

55                33

55                33

57                6

57                6

Any help would be appreciated.

Why do you need four rows? Wouldn't 2 rows with a count provide the same information? — Gordon Linoff
– Gordon Linoff, Commented Jan 28, 2019 at 20:17

Lasse V. Karlsen · Accepted Answer · 2019-01-29 13:01:21Z

2

You can use the following query that will return the distinct Serial Number which have the multiple Cycle Number with same value.

SELECT SerialNumber, CycleNumber
FROM Table
GROUP BY SerialNumber, CycleNumber HAVING COUNT(SerialNumber)>1

edited Jan 29, 2019 at 13:01

Lasse V. Karlsen

394k107 gold badges651 silver badges845 bronze badges

answered Jan 29, 2019 at 12:15

Mano

7883 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2019-01-28 20:18:56Z

1

If you really only have two columns, you could do:

select serial, cycle
from t
where (serial, cycle) in (select serial, cycle
                          from t t2
                          group by serial, cycle
                          having count(*) >= 2
                         );

But why wouldn't you just do this?

select serial, cycle, count(*)
from t
group by serial, cycle;

answered Jan 28, 2019 at 20:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Comments

Brijesh · Accepted Answer · 2019-01-29 12:58:31Z

0

SQL Syntax:

SELECT A.* FROM tmpTable A
LEFT JOIN
( 
SELECT SerialNumber,CycleNumber FROM tmpTable  GROUP BY  SerialNumber, CycleNumber having count(SerialNumber)>1
) B ON A.SerialNumber = B.SerialNumber And A.CycleNumber = B.CycleNumber
WHERE B.SerialNumber is not null

answered Jan 29, 2019 at 12:58

Brijesh

5294 silver badges13 bronze badges

Collectives™ on Stack Overflow

SQL command for distinct values in one column and multiple same values in another

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related