Help with this SQL please 1

[mayamanako]

hi guys, could you please help me out with this query. im a bit confused and can't seem to get my head round this problem.

my table is like this

frequency  myText  thedate
1           apple   Aug 01, 11
1           pear    Aug 01, 11
1           grapes  Aug 01, 11
3           grapes  Aug 02, 11
1           melon   Aug 02, 11
1           berry   Aug 03, 11
1           apple   Aug 03, 11
1           pear    Aug 03, 11
1           pear    Aug 04, 11
2           kiwi    Aug 04, 11
1           cherry  Aug 05, 11

then, i want everything that occurs only once be taken off the list and list only the ones which have more than 1 occurence.

i want a recordset that's something like this:

frequency  myText  thedate
1           apple   Aug 01, 11
1           pear    Aug 01, 11
1           grapes  Aug 01, 11
3           grapes  Aug 02, 11
1           apple   Aug 03, 11
1           pear    Aug 03, 11
1           pear    Aug 04, 11
2           kiwi    Aug 04, 11

thanks very much for any help.

 

[mayamanako]

im sorry, i think ive posted twice.. forum admin, please can you delete my first post. thanks.

 

[gmmastros]

Try this:

Select T.*
From   YourTableNameHere T
       Left Join (
         Select myText
         From   YourTableNameHere
         Group By myText
         Having Sum(Frequency) = 1
         ) As A
         On T.myText = A.myText
Where  A.myText Is NULL

If this works for you, and you would like me to explain, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mayamanako]

hi gmmastros, it worked!!!

thanks very much