Select selected record

Transact-SQL
#1

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,

#2

-- try this

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

create table #test ( 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 desc) as RN
,
,[trans id]
,[rec.no]
from #test
),cte2 as (
select * from cte
where rn = 1
)
select

,[trans id]
,[rec.no]
from cte2

#3

thx a lot

#4

Hi

Another way of doing this

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

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

#5

great thx

#6

thx...great..

#7

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

#8

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

thx

#9

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

#10

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

#11

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:

#12

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

#13

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

#14

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