by occurence

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

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.brussels@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
>

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.