none
sql query to get latest row values

    Question

  • Hi,

    we are using sql server 2005.

    I have 2 tables related by common id. Main table will have 1 unique row per transaction, but child table can have multiple transactions related to main table transaction as the process is done like this.

    Main Table

    MID

    CommonID

    Date

    type

    Status

    1

    11

    06/12/2012

    BK

    Complete

    2

    12

    06/12/2012

    BK

    Complete

    3

    13

    06/12/2012

    BK

    Repair

    4

    14

    06/11/2012

    WR

    Complete

    Child Table

    CID

    CommonID

    Version

    Amount

    Desc

    1

    11

    0

    2.00

    Txn complete

    2

    12

    0

    2.00

    Entered

    3

    12

    1

    3.00

    Repaired

    4

    13

    0

    2.00

    Chd tble row0

    5

    13

    1

    2.00

    Chd tble row1

    6

    13

    2

    2.00

    Chd tble row2

    7

    14

    0

    5.00

    Good day

    8

    14

    1

    5.00

    Paid to client

    In the above example, Main Table row with CommonID 12 has 2 rows in Child table. 13 has 3 rows and 14 has 2 rows. I need a query to get the latest transaction of each commonID with max version. So, my result set should be like this below:

    CID

    Amount

    Type

    Status

    Desc

    1

    2.00

    BK

    Complete

    Txn complete

    3

    3.00

    BK

    Complete

    Repaired

    6

    2.00

    BK

    Repair

    Chd tble row2

    8

    5.00

    WR

    Complete

    Paid to client

    In summary, I need to get latest version row from child table.

    I appreciate your response.

    Thanks,

    Spunny

    Tuesday, June 12, 2012 5:31 PM

Answers

  • declare @t table(CID int,CommonID int,Version int,Amount numeric(17,2),Type varchar(10),Status varchar(20), Descr varchar(50)) insert into @t values(1,11,0,2.00,'BK','Complete','Txn complete') insert into @t values(2,12,0,2.00,'BK','Complete','Entered') insert into @t values(3,12,1,3.00,'BK','Complete','Repaired') insert into @t values(4,13,0,2.00,'BK','Repair','chd tble row0') insert into @t values(5,13,1,2.00,'BK','Repair','chd tble row1') insert into @t values(6,13,2,2.00,'BK','Repair','chd tble row2') insert into @t values(7,14,0,5.00,'WR','Complete','Good Day') insert into @t values(8,14,1,5.00,'WR','Complete','Paid to client') ;with cte as ( select CID,Amount,Type,Status,Descr, row_number() over (partition by CommonID order by Version desc) as rn from @t ) select CID,Amount,Type,Status,Descr from cte where rn=1

    RESULTS:

    --------

    CID Amount Type  Status  Descr
    1 2.00  BK  Complete  Txn complete
    3 3.00  BK  Complete  Repaired
    6 2.00  BK  Repair  chd tble row2
    8 5.00  WR  Complete  Paid to client



    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, June 13, 2012 1:03 AM
  • Hi,

    I gave you an idea on how you are getting the latest version, use row_number to get a ranking depending on version, here is the sample



    with cte as
    (
    select CID,Amount,Type,Status,Descr,
    row_number() over (partition by CommonID order by Version desc) as rn
    from finalTable
    )
    select CID,Amount,Type,Status,Descr from cte where rn=1---Remove this where condition 
    --and look at the result, you will come to know how the logic works :)


    Regards
    Satheesh

    Wednesday, June 13, 2012 2:21 AM

All replies

  • try this code

    with cte as 
    (select CID, CommonID, Version, Amount, [Desc],
     Row_number() OVER(PARTITION BY cOMMONID order by cOMMONID,VERSION DESC) as RN
     from child)
    select C.CID,Amount,Type,Status,[Desc] from CTE C inner join MAIN on C.COMMONID=MAIN.CID and C.RN=1

    Regards
    satheesh


    Tuesday, June 12, 2012 5:41 PM
  • Hi

    this is a tested version

    with cte as 
    (select CID, CommonID, Version, Amount, [Desc],
     Row_number() OVER(PARTITION BY cOMMONID order by cOMMONID,VERSION DESC) as RN
     from child)
    select C.CID,Amount,Type,Status,[Desc] from CTE C inner join MAIN on C.COMMONID=MAIN.CID and C.RN=1

    regards
    satheesh

    Tuesday, June 12, 2012 6:06 PM
  • Hi satheesh,

    Thanks for reply and sorry for asking again. It is a long query based on conditions written by someone else. I am making edits to the query to meet above requirement. The query is done as dynamic sql and executed using sp_executesql and inserted into tmp table. This tmp table is joined with some other tables to get final result. So, Can you please give query for the below tmp table:

    Child Table

    CID

    CommonID

    Version

    Amount

    Type

    Status

    Desc

    1

    11

    0

    2.00

    BK

    Complete

    Txn complete

    2

    12

    0

    2.00

    BK

    Complete

    Entered

    3

    12

    1

    3.00

    BK

    Complete

    Repaired

    4

    13

    0

    2.00

    BK

    Repair

    Chd tble row0

    5

    13

    1

    2.00

    BK

    Repair

    Chd tble row1

    6

    13

    2

    2.00

    BK

    Repair

    Chd tble row2

    7

    14

    0

    5.00

    WR

    Complete

    Good day

    8

    14

    1

    5.00

    WR

    Complete

    Paid to client

    If this is the final tmp table data, please let me know how to get latest version rows only like below

    CID

    Amount

    Type

    Status

    Desc

    1

    2.00

    BK

    Complete

    Txn complete

    3

    3.00

    BK

    Complete

    Repaired

    6

    2.00

    BK

    Repair

    Chd tble row2

    8

    5.00

    WR

    Complete

    Paid to client

    Thanks,

    Spunny

    Tuesday, June 12, 2012 7:17 PM
  • if you dont want to use CTE you can use..

    DROP

    TABLE [dbo].[#TMP]

    GO

    CREATE

    TABLE [dbo].[#TMP] (

    [ID]

    int NOT NULL

    ,

    [Name] varchar(100) NOT NULL

    )

    go

    DROP

    TABLE [dbo].[#TMP1]

    GO

    CREATE

    TABLE [dbo].[#TMP1] (

    [ID]

    int NOT NULL

    ,

    [TMPID] int NOT NULL

    ,

    [Value] int NOT NULL

    )

    GO

    Insert

    into #TMP

    Select

    1,'D1' Union

    Select

    2, 'D2'

    Insert

    into #Tmp1

    Select

    1,1,10 Union

    Select

    2,1,20 Union

    Select

    3,1,30 Union

    Select

    4,2,15 union

    Select

    5,2,25

    Select

    * from

    (

    Select

    B.*, Row_Number() OVER(PARTITION BY b.TMPID order by b.TMPID,b.ID DESC ) as RNum

    from

    #Tmp a, #tmp1 b

    where

    a.id = b.tmpid

    )

    a

    where

    RNum = 1

    Tuesday, June 12, 2012 11:26 PM
  • declare @t table(CID int,CommonID int,Version int,Amount numeric(17,2),Type varchar(10),Status varchar(20), Descr varchar(50)) insert into @t values(1,11,0,2.00,'BK','Complete','Txn complete') insert into @t values(2,12,0,2.00,'BK','Complete','Entered') insert into @t values(3,12,1,3.00,'BK','Complete','Repaired') insert into @t values(4,13,0,2.00,'BK','Repair','chd tble row0') insert into @t values(5,13,1,2.00,'BK','Repair','chd tble row1') insert into @t values(6,13,2,2.00,'BK','Repair','chd tble row2') insert into @t values(7,14,0,5.00,'WR','Complete','Good Day') insert into @t values(8,14,1,5.00,'WR','Complete','Paid to client') ;with cte as ( select CID,Amount,Type,Status,Descr, row_number() over (partition by CommonID order by Version desc) as rn from @t ) select CID,Amount,Type,Status,Descr from cte where rn=1

    RESULTS:

    --------

    CID Amount Type  Status  Descr
    1 2.00  BK  Complete  Txn complete
    3 3.00  BK  Complete  Repaired
    6 2.00  BK  Repair  chd tble row2
    8 5.00  WR  Complete  Paid to client



    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Wednesday, June 13, 2012 1:03 AM
  • Hi,

    I gave you an idea on how you are getting the latest version, use row_number to get a ranking depending on version, here is the sample



    with cte as
    (
    select CID,Amount,Type,Status,Descr,
    row_number() over (partition by CommonID order by Version desc) as rn
    from finalTable
    )
    select CID,Amount,Type,Status,Descr from cte where rn=1---Remove this where condition 
    --and look at the result, you will come to know how the logic works :)


    Regards
    Satheesh

    Wednesday, June 13, 2012 2:21 AM