insert into #test values
('2018-05-10','001',8)
,('2018-03-01','001',7)
,('2018-02-01','001',6)
,('2018-05-10','002',8)
,('2018-04-11','002',7)
,('2018-05-11','003',5)
--original
select * from #test
--desired result
;with cte as(
select
ROW_NUMBER() over (partition by [trans id] order by desc) as RN
,
,[trans id]
,[rec.no]
from #test
),cte2 as (
select * from cte
where rn = 1
)
select
drop table test
go
create table test ( Invaliddate date,[transid] varchar(8) ,[recno] int)
insert into test values
('2018-05-10','001',8)
,('2018-03-01','001',7)
,('2018-02-01','001',6)
,('2018-05-10','002',8)
,('2018-04-11','002',7)
,('2018-05-11','003',5)
go
select * from test
go
SQL
Select transid,count(recno) from test
Group by transid
having count(recno) > 1
the recno showing duplicate records so I want to show records that have recno >=2 for the same teacher. And it shows the 1,2,3 , and so on records . But it will not show the record that only have recno=1
Sorry I believe there's some miss understanding..The Topics that I try find the solution is on
" Select only the double or more record " subject. not on this one.. so sorry for my respond
thx
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
insert into #test values
('2018-05-10','001',8)
,('2018-03-01','001',7)
,('2018-02-01','001',6)
,('2018-05-10','002',8)
,('2018-04-11','002',7)
,('2018-05-11','003',5)
select distinct a.* from #test a join
(
select row_number() over(partition by month(Invaliddate) order by month(Invaliddate) desc) as rnk,* from #test
) b
on month(b.Invaliddate) = month(a.Invaliddate)
where b.rnk>=2