Ilyas Ilyas - 15 days ago 6
SQL Question

Select records with fewer than 10 entries sql server

i want only to display the The ID which have record less than 10 entries for each ID, an ID may have several values as you see in the data below. i want
i have tried this query but it selects also the record for ID 2

select ID, Name ,LastName ,PaymentDate,POSITION
From ( select ID, Name ,LastName ,PaymentDate ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY PaymentDate DESC) AS POSITION

)
where Position < 10


any help please

ID Name LastName PaymentDate
1 John Abraham 2015-05-08
1 John Abraham 2014-05-08
1 John Abraham 2013-05-08
1 John Abraham 2012-05-08
1 John Abraham 2011-05-08
1 John Abraham 2010-05-08
------------------------------
2 Adam White 2015-05-08
2 Adam White 2014-05-08
2 Adam White 2013-05-08
2 Adam White 2012-05-08
2 Adam White 2011-05-08
2 Adam White 2010-05-08
2 Adam White 2009-05-08
2 Adam White 2008-05-08
2 Adam White 2007-05-08
2 Adam White 2006-05-08
2 Adam White 2005-05-08
2 Adam White 20004-05-08

Answer

Please try:

Select ID, Name, LastName, PaymentDate
  From MyTable
 Where ID in (Select ID From MyTable Group By ID Having Count(*) < 10);
Comments