SQL duplicated rows selection without using "having"

Question

I have this type of table:

A.code        A.name
   1.            X
   2.            Y
   3.            X
   4.            Z
   5.            Y

And i need to write a query that gives me all duplicated names like this:

 A.name
   X
   Y
   Z

Without using "group by".

Because its my first SQL querys exercise and our lecturer instructed us to not use it (cause we havent learn it yet) — E25, Nov 16 '15 at 20:23
I feel like using a group by is way easier than not using a group by — AznDevil92, Nov 16 '15 at 20:25
I dont understand, im doing it for my own practice, no one is chacking on me and I want to expand my knowledge. — E25, Nov 16 '15 at 20:37
You say you can't use `GROUP BY` because your instructor said so, but that you're doing this exercise for your own practice? How does that work? — Mage Xy, Nov 16 '15 at 21:11
Possible duplicate of [Is there any difference between GROUP BY and DISTINCT](http://stackoverflow.com/questions/164319/is-there-any-difference-between-group-by-and-distinct) — jophab, Dec 16 '16 at 14:32

score 2 · Answer 1 · answered Apr 12 '18 at 22:31

The correlated subquery is your friend here. The subquery is evaluated for every row in the table referenced in the outer query due to the table alias used in both the outer query and the subquery.

In the subquery, the outer table is queried again without the alias to determine the row's compliance with the condition.

SELECT DISTINCT name FROM Names AS CorrelatedNamesTable 
WHERE 
(
     SELECT COUNT(Name) FROM Names WHERE Name = CorrelatedNamesTable.Name
) > 1

score 1 · Answer 2 · edited Nov 26 '16 at 01:10

1

Try using DISTINCT for the column. Please note in tables with a large number of rows, this is not the best performance option.

SELECT DISTINCT A.Name FROM A

edited Nov 26 '16 at 01:10

fragilewindows

1,394
1
15
26

answered Nov 30 '15 at 20:05

RJD

67
5

2

This won't answer the question. It's asking for the duplicates, not the distinct values. – dwilli May 11 '18 at 21:47

score 0 · Answer 3 · answered Nov 20 '15 at 07:58

0

SELECT a1.name FROM A a1, A a2 WHERE a1.name=a2.name AND a1.code<>a2.code

This assumes code is unique ;).

answered Nov 20 '15 at 07:58

Mikey

629
1
11
19

interesting. that is the first time I've seen that notation before. appears to be the same as a join. – mcfea Jan 06 '16 at 23:43
This an old (and deprecated) way of doing a "cross join". It's still valid syntax, but not best practice. – reasonet Jun 05 '19 at 21:15

SQL duplicated rows selection without using "having"

3 Answers3