Show only results in query with more then 2 hits

Question

didnt find a solution yet... ;-)

suppose this is the result of a query:

ID, SUBS, DATE . 
19967,142, 2017-06-15 . 
20214,147, 2017-05-09 .
20214,137, 2017-06-07 . 
20303,147, 2017-05-31 .

and i want only the records with an ID who are more then 1 time in result, but i do want the 2 or more records, so not GROUP BY.

How to manipulate the query?

What query? You haven't shown anything aside from some unformatted results. (I formatted it, but still - not sure what you're asking) — David Makogon, Jul 03 '17 at 20:52
i'm sorry not really familiar with the format here, hope this is beter... excuse me — Anton Timmerman, Jul 03 '17 at 20:55
Ideally, a querstion like this would include an SQL Fiddle or equivalent with sample data and queries. As is, we can't show you a real query against the data without essentially making up the tables and queries. — erik258, Jul 03 '17 at 20:59

score 0 · Answer 1 · answered Jul 03 '17 at 20:59

0

You can use exists, assuming the dates are different:

select t.*
from t
where exists (select 1
              from t t2
              where t2.id = t.id and t2.date <> t.date
             );

Based on the context of the question, I interpret "more than 2" to actually be "2 or more". Otherwise an aggregation would be necessary.

answered Jul 03 '17 at 20:59

Gordon Linoff

1,242,037
58
646
786

I wonder if that might be faster than enumerating the ones that are different. Not sure if date comparison or count ( my answer ) would be more efficient. – erik258 Jul 03 '17 at 21:08
You can try the two for comparison, but this should be faster with an index on `t(id, date)`. – Gordon Linoff Jul 03 '17 at 21:15

score 0 · Answer 2 · answered Jul 03 '17 at 21:07

You'll have to do it in 2 steps - one to determine which IDs have multiples and another to select them.

There's many ways to do that; in this version, the ids having multiples are counted and then the data is selected separately.

In my (outdated) experience, large IN sets can be pretty slow. If that list ends up being huge, it would make sense to use a temporary table to store the results so they can be indexed. Regardless, an index on id seems like it could only help.

select t.* from t where t.id in ( select id, count(*) c from t having c > 1)

Ondrasek Koudel Kubelka · Answer 3 · 2017-07-03T21:40:32.083

0

SELECT * FROM test WHERE id IN (SELECT id FROM test GROUP BY id HAVING COUNT(id) > 1);

edited Jul 03 '17 at 21:40

answered Jul 03 '17 at 21:29

Ondrasek Koudel Kubelka

9
4

score -1 · Answer 4 · answered Jul 03 '17 at 21:15

create a table where it shows the number of occurrence of a record called sumRecords, and left join sumRecords with constraints as WHERE occurrence > 1

I am referring your table as temp Here is the code:

SELECT temp.* FROM 
temp LEFT JOIN 
(SELECT ID, COUNT(*) occurance
FROM temp
GROUP BY ID) sumRecords ON temp.ID = sumRecords.ID
WHERE sumRecords.occurance > 1

result:

ID      subs     dat
20214   147     2017-05-09
20214   137     2017-06-07

Show only results in query with more then 2 hits

4 Answers4