Select selected record

Hi,
I have some record as below
Date Trans ID rec.no
2018-5-10 001 8
2018-3-1 001 7
2018-2-1 001 6
2018-5-10 002 8
2018-4-11 002 7
2018-5-11 003 5

I want to select it into new result
Date Trans ID rec.no
2018-5-10 001 8
2018-5-10 002 8
2018-5-11 003 5

only the latest record will appear..
what command that should I put?

thx,

-- try this

if object_id('tempdb..#test') is not null drop table #test

create table #test ( [datex] date,[trans id] varchar(8) ,[rec.no] 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)

--original
select * from #test

--desired result
;with cte as(
select
ROW_NUMBER() over (partition by [trans id] order by [datex] desc) as RN
,[datex]
,[trans id]
,[rec.no]
from #test
),cte2 as (
select * from cte
where rn = 1
)
select
[datex]
,[trans id]
,[rec.no]
from cte2

thx a lot

Hi

Another way of doing this

Select transid,rec no , max(date)
Group by transid,rec no

If it helps
Great
,,:grinning::grinning:

great thx

thx...great..

it's only show the 2nd record, I need to know the 1st and 2 nd or 3th records..

it is show all of the records..I only need to show the duplicate records

thx

which column duplicates do you want ????

if you want recno column dups

drop create data ...
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
Result

image

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

thx

hi

please i am not able to understand ....
.. this is very easy to write SQL if i understand what you want ... !!!

please explain using diagrams if possible
thx
:slight_smile:
:slight_smile:

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

HI JOE

SOLUTION FOR YOUR OTHER POST

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

if object_id('tempdb..#test') is not null drop table #test

create table #test ( Invaliddate date,[transid] varchar(8) ,[rec.no] 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)

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