I need to query my database to show the records inside my table where lastname occurs more than three times. Example: in my Students Table, there are 3 people with Lastname ‘Smith’, 4 with ‘Johnson’, and 1 with ‘Potter’. My query should show the records of those with the lastnames Smith, and Johnson since these values occur more than or equal to 3 times.
Can anyone point me to this? I was thinking of using COUNT() but I can’t seem to think how to apply it?
Advertisement
Answer
For SQL Server 2005+
;WITH T AS ( SELECT *, COUNT(*) OVER (PARTITION BY Lastname) as Cnt FROM Students ) SELECT * /*TODO: Add column list. Don't use "*" */ FROM T WHERE Cnt >= 3