SQLTeam.com | Weblogs | Forums

Select only the double or more record


#1

Hi,
I have some record as below
Date Teacher rownum
01-01-2019 William 1
02-01-2019 Brandon 1
01-01-2019 William 2
02-01-2019 Edith 1
02-01-2019 John 1
02-01-2019 John 2

I want to show record that only have more than 1 record so the result will be

Date Teacher rownum
01-01-2019 William 1
01-01-2019 William 2
02-01-2019 John 1
02-01-2019 John 2

So what is the command...

Thx,

Joe


#2
DROP CREATE DATA
DROP TABLE data
GO 


CREATE TABLE data
(

DateOK VARCHAR(100), 
Teacher VARCHAR(100), 
rownum  INT ,
)
GO 

insert into data select '01-01-2019', 'William', 1
insert into data select '02-01-2019', 'Brandon', 1
insert into data select '01-01-2019', 'William', 2
insert into data select '02-01-2019', 'Edith', 1
insert into data select '02-01-2019', 'John', 1
insert into data select '02-01-2019', 'John', 2
GO
SQL ...
SELECT B.* FROM DATA B JOIN 
(
SELECT * FROM data 
WHERE ROWNUM >=2
) A  
ON B.Teacher = A.Teacher

image


#3

great thx