Row_number() in Apply clause. - SQL Server Forums

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)

Row_number() in Apply clause.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-08-13 : 04:19:41

Hi guys I have a query like this
(
Select id,row_number() over(order by t1.vehicleregid,t1.[Transaction Start Time]) as id1,s1.[Site Name],t1.VehicleRegID,v1.[Vehicle Registration],
t1.[transaction volume] As loadtransaction,
t2.[transaction volume] As unloadtransaction,t1.[Average Solids],t1.[Transaction Start Time] From VmData t1
Inner Join vehicleregistration v1 On t1.VehicleRegID=v1.VehicleRegID
Inner Join sitename s1 On t1.SiteID=s1.SiteID
outer Apply
(
Select [transaction volume] From VmData Where id=t1.id+1 and VehicleRegID=t1.VehicleRegID )t2
)

There is a table called VMdata which has a identity column named id.
In the above query as you can see in table t2 I am comparing with the identity column of VMData.
What I need is I need to compare with the id1 of VmData t1 and the same for t2.
I mean sth lk this

(
Select id,row_number() over(order by t1.vehicleregid,t1.[Transaction Start Time]) as id1,s1.[Site Name],t1.VehicleRegID,v1.[Vehicle Registration],
t1.[transaction volume] As loadtransaction,
t2.[transaction volume] As unloadtransaction,t1.[Average Solids],t1.[Transaction Start Time] From VmData t1
Inner Join vehicleregistration v1 On t1.VehicleRegID=v1.VehicleRegID
Inner Join sitename s1 On t1.SiteID=s1.SiteID
outer Apply
(
Select [transaction volume] From VmData Where over(order by t1.vehicleregid,t1.[Transaction Start Time] as id2
=t1.id1+1 and VehicleRegID=t1.VehicleRegID )t2
)

Thanks for any help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 04:30:17

Didnt understand what you're looking at? do you mean your current query has an error? or are you asking for better method? also please post some sample data of what you desire as output?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-08-13 : 04:35:31

Thanks Vishakh for the reply.I will try to clarify it better.
For example a table t1 has a identity column id but I have a another column say vehicleid.I used row_number function to get a vehicleids in order as id1.But in outer query of the apply clause I need to compare the value with id1.
example I dont want this

Select id,row_number() over(order by t1.vehicleregid) as id1 from VmData t1
outer Apply
(
Select [transaction volume] From VmData Where id=t1.id+1 and VehicleRegID=t1.VehicleRegID )t2
)

I want sth like

Select id,row_number() over(order by t1.vehicleregid) as id1 from VmData t1
outer Apply
(
Select [transaction volume] From VmData Where row_number() over(order by vehicleregid) as id2 =t1.id1+1 and VehicleRegID=t1.VehicleRegID )t2
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 04:38:07

try like this:-


;
With CTE (id,seq,SiteName,VehicleRegID,VehicleRegistration,transactionvolume,AverageSolids,TransactionStartTime) AS
(
Select id,row_number() over(order by t1.vehicleregid,t1.[Transaction Start Time]) as id1,s1.[Site Name],t1.VehicleRegID,v1.[Vehicle Registration], 
t1.[transaction volume] As loadtransaction,t1.[Average Solids],t1.[Transaction Start Time] From VmData t1
Inner Join vehicleregistration v1 On t1.VehicleRegID=v1.VehicleRegID
Inner Join sitename s1 On t1.SiteID=s1.SiteID
)

select c.id,c.seq,
c.SiteName,
c.VehicleRegID,
c.VehicleRegistration,
c.transactionvolume as loadtransaction,
c1.transactionvolume as unloadtransaction,
c.AverageSolids,
c.TransactionStartTime
from CTE c
outer apply (select transactionvolume from cte where seq=c.seq+1 and VehicleRegID=c.VehicleRegID)c1

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-08-13 : 05:18:02

quote:

Originally posted by visakh16

try like this:-


;
With CTE (id,seq,SiteName,VehicleRegID,VehicleRegistration,transactionvolume,AverageSolids,TransactionStartTime) AS
(
Select id,row_number() over(order by t1.vehicleregid,t1.[Transaction Start Time]) as id1,s1.[Site Name],t1.VehicleRegID,v1.[Vehicle Registration], 
t1.[transaction volume] As loadtransaction,t1.[Average Solids],t1.[Transaction Start Time] From VmData t1
Inner Join vehicleregistration v1 On t1.VehicleRegID=v1.VehicleRegID
Inner Join sitename s1 On t1.SiteID=s1.SiteID
)

select c.id,c.seq,
c.SiteName,
c.VehicleRegID,
c.VehicleRegistration,
c.transactionvolume as loadtransaction,
c1.transactionvolume as unloadtransaction,
c.AverageSolids,
c.TransactionStartTime
from CTE c
outer apply (select transactionvolume from cte where seq=c.seq+1 and VehicleRegID=c.VehicleRegID)c1

Thanks Vishak it did the trick.But do I have to use the CTE because already I am using the resultset of the apply clause in another CTE.Cant it be done in the apply clause somehow.Because the above query is some part of a larger query & I am using 3 Cte already.With another one it will 4.Will there be any performance issue?Please suggest.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources