Need to show the Latest Record in the List

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need to show the Latest Record in the List

Author

Topic

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-10 : 05:51:34

Hi,

I want to keep only latest version records and dont want previous version records.

Actual Query & Result:
select a.book_no,b.ticket_no,c.amend_no,c.versionno from booking a (nolock)
left outer join ticket b (nolock) on a.book_no=b.book_no
left outer join amend_ticket c (nolock) on b.ticket_no=c.ticket_no
where a.load='maa' and a.discharge='bng'
and a.datecr>='2008-07-01' and a.datecr<='2008-07-20' and a.status<>'v'

BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA221BNG AMD100 1
MAA117 MAA221BNG AMD101 2
MAA117 MAA221BNG AMD102 3
MAA117 MAA221BNG AMD103 4
MAA120 MAA535BNG AMD225 1
MAA120 MAA535BNG AMD226 2
MAA120 MAA535BNG AMD227 3

Expecting Result:
BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA221BNG AMD103 4
MAA120 MAA535BNG AMD227 3

i want show only the last record as result like above and remaining 3 versions should not be shown in the result.

Kindly assist me how to solve this problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 05:53:53

[code]SELECT columns...
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY BookNo, TicketNo ORDER BY VersionNo DESC) AS Seq,*
)t
WHERE seq=1
[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 05:55:27

select * from
(select *,row_number() over (partition by bookno order by amendmentno desc) as rowno from urtable) t
where t.rowno = 1

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 05:59:58

[code]
select b.* from
(select bookno, max(version) as maxver from urtable group by bookno) a
cross apply
(select * from urtable where bookno = a.bookno and version = a.maxver)b

[/code]

Jai Krishna

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-10 : 06:13:49

Thank you so much Visakh and JaiKrishna for you quick response.

i just had doubt when there will be multiple tickets for a booking.

BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA2100BNG AMD100 1
MAA117 MAA2100BNG AMD111 2
MAA117 MAA2111BNG NULL NULL
MAA117 MAA2120BNG NULL NULL

Your advised query showing latest versions perfectly. But, if the bookno is like above with multiple tickets then also its showing only 2nd version (latest) and not showing other two ticket which are not amended.

Result should be like this:

BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA2100BNG AMD111 2
MAA117 MAA2111BNG NULL NULL
MAA117 MAA2120BNG NULL NULL

Kindly assist.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 06:15:20

quote:
Originally posted by ganny

Thank you so much Visakh and JaiKrishna for you quick response.

i just had doubt when there will be multiple tickets for a booking.

BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA2100BNG AMD100 1
MAA117 MAA2100BNG AMD111 2
MAA117 MAA2111BNG NULL NULL
MAA117 MAA2120BNG NULL NULL

Your advised query showing latest versions perfectly. But, if the bookno is like above with multiple tickets then also its showing only 2nd version (latest) and not showing other two ticket which are not amended.

Result should be like this:

BookNo TicketNo AmendmentNo Version
------------------------------------------------------
MAA117 MAA2100BNG AMD111 2
MAA117 MAA2111BNG NULL NULL
MAA117 MAA2120BNG NULL NULL

Kindly assist.

select * from
(select *,row_number() over (partition by bookno,ticketno order by amendmentno desc) as rowno from urtable) t
where t.rowno = 1

Jai Krishna

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-10 : 06:17:30

[code]
select b.* from
(select bookno, ticketno,max(version) as maxver from urtable group by bookno,ticketno) a
cross apply
(select * from urtable where bookno = a.bookno and version = a.maxver and ticketno = a.ticketno)b

[/code]

Jai Krishna

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 06:21:12

i've already included it. see my earlier suggstion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 06:27:30

[code]select *
into #Temp
from
(

------------------------------------------------------
SELECT 'MAA117' AS BookNo,'MAA2100BNG' AS TicketNo,'AMD100' AS AmendmentNo, 1 AS Version UNION ALL
SELECT 'MAA117' ,'MAA2100BNG', 'AMD111', 2 UNION ALL
SELECT 'MAA117', 'MAA2111BNG', NULL, NULL UNION ALL
SELECT 'MAA117', 'MAA2120BNG', NULL, NULL

)t

select BookNo,TicketNo,AmendmentNo,Version
from
(select row_number() over (partition by BookNo,TicketNo ORDER BY Version DESC) AS Seq,*
FROM #temp
)t
where seq=1

drop table #temp

output
---------------------------------------
BookNo TicketNo AmendmentNo Version
MAA117 MAA2100BNG AMD111 2
MAA117 MAA2111BNG NULL NULL
MAA117 MAA2120BNG NULL NULL[/code]

ganny
Yak Posting Veteran

51 Posts

Posted - 2009-02-10 : 06:32:14

Yes, i got the results. Thank you very much for your valuable assistance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-10 : 06:36:27

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources