On Mar 26, 4:59 pm, "Sean" <sean.anderson@[nospam]oakleafgroup.biz>
wrote:
> Not 100% sure of this in MySQL ........
>
> SELECT T1.*
> FROM TEST T1
> JOIN
> (
> SELECT B, COUNT(B) AS NUM
> FROM TEST
> GROUP BY B
> ) T2 ON T1.B = T2.B WHERE T2.NUM > 1
>
> <upx.bruss...@gmail.com> wrote in message
>
> news:1174923412.070292.238380@p15g2000hsd.googlegr oups.com...
>
> > Hello all
>
> > if i have a table containing the follwing
>
> > Id --Value
> > 0 -- 15
> > 1 -- 5
> > 2 -- 15
> > 3 -- 4
> > 4 -- 9
> > 5 -- 1
> > 6 -- 4
>
> > how can i obtains easily only values repeated more than only one
> > (where occurence >1)
> > result:
>
> > Id --Value
> > 0 -- 15
> > 2 -- 15
> > 3 -- 4
> > 6 -- 4
>
> > thanks in advance

A subquery is an inefficient way to do this. In the particular case of
selecting non-unique values the correct way would be like this:

SELECT t1 . *
FROM `test1` t1
LEFT JOIN `test1` t2 ON t1.value = t2.value
AND t1.id <> t2.id
WHERE t2.value IS NOT NULL
ORDER BY t1.value DESC ,t1. id;

For a more general case, in which you want to select where greater
than 2 or greater than 3, the answer posted on the mysql forum (http://
forums.mysql.com/list.php?10) is more like the way to go.