CODEWITHSUNDEEP
sqlsql-serversubquery
Hot Cool Stud
Hot Cool Stud

Reputation: 1205

Select query on temp table without using cursor SQL

I want perform SQL query on table schema 

Id | BusNo | PartNo |dateAdded
1  | 437   | 2      |2014-02-28
2  | 423   | 3      |2014-03-28
3  | 423   | 3      |2014-04-28
4  | 437   | 2      |2014-03-28
5  | 452   | 1      |2014-03-29

I would like to select results top ID order by date with where condition on BusNo and PartNo.Result would be like this

Id | BusNo | PartNo |dateAdded
3  | 423   | 3      |2014-04-28
4  | 437   | 2      |2014-03-28
5  | 452   | 1      |2014-03-29

I tried 

select [Id] 
into  
from [PartUsed] 
where BusNo = @busNo and [PartNo] exists (select ID from @Usertable)

@userTable is user defined table type, but it will select all rows and I want top 1 in partNo group order by dateAdded.

Upvotes: 0

Views: 95

Answers (2)

Mudassir Hasan
Mudassir Hasan

Reputation: 28751

With cte as ( Select id,busno,partno,dateadded,
              Row_Number() over( partition by partno order by dateadded desc ) as seqNum
              from Partused
             )

select id,busno,partno,dateadded
from cte
where seqNum=1

Upvotes: 1

dean
dean

Reputation: 10098

    ;with x as (
    select *, row_number() over(partition by PartNo order by DateAdded desc) as rn
    from PartsUsed
    )
    select *
    from x
    where x.BusNo = @busNo
    and x.PartNo in (select ID from @Usertable) 
    and x.rn = 1

Upvotes: 1

Related Questions