SQL Server - i have a table X, has fields date and transNo .I want to select date, max(transNo)<date

Asked By Gayathri S on 28-Mar-15 05:24 AM

i have a table X, has fields date and transNo .I want to select date, max(transNo)<date
eg eg X
Date TransNo
1/4/2026 1
1/4/2026 2
2/4/2026 3
2/4/2026 4
2/4/2026 5
3/4/2026 6
4/4/2026 7

I want to get the result like this

2/4/2026 2
3/4/2026 5
4/4/2026 6
pls help me ...

arockiaraj a replied to Gayathri S on 03-Jun-15 11:58 PM

USE SELF JOIN

1.create table

create table X(transno int identity(1,1),date datetime)

2.insert value

insert into X values ('20140401'),('20140401'),('20140402'),
('20140402'),('20140402'),('20140403'),('20140404')

3.view values

select * from X

4. SELF JOIN QUERY

select a.transno,b.date from x as a,X as b
where (a.transno+1)=b.transno
and a.date < b.date