SQL Query To Obtain Value that Occurs more than once
From Oracle (but works in most SQL DBs):
SELECT LASTNAME, COUNT(*)
FROM STUDENTS
GROUP BY LASTNAME
HAVING COUNT(*) >= 3
P.S. it's faster one, because you have no Select withing Select methods here
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