compare sql queries

arkiboys 9,366 Reputation points
2023-07-12T19:39:29.5333333+00:00

hello, my question is related to tsql but do not find a forum for it specifically. Hope this forum is ok to post it here...

I have written a tsql equivalent (2nd section below) for the first sql (mentioned first which is postgress sql). Can you see why my tsql at the end returns different result to that in the first sql?

Thanks

select * 
      from  
      ( 
         select * 
         from "audit"."tblHistory"
         inner join 
         ( 
            select "LoginId", max("Timestamp") "Timestamp" 
            from "audit"."tblHistory" 
            group by "LoginId"
         ) a 
         using("LoginId", "Timestamp") 
      ) b 
      where "Update" = 'insert' 
-------------------------------------
SELECT
      *
   FROM
      (
      SELECT 
         "LoginId", "Timestamp", "Update"
         FROM
         (
         select 
            *
            , row_number() OVER (partition BY "LoginId", "Timestamp" order BY "Timestamp" desc) as ROW_NUMBER
         FROM
            audit."tblHistory"
         ) as a
         WHERE
            a.ROW_NUMBER = 1
      ) as b
      WHERE
         b."Update" = 'insert'
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
{count} votes

Accepted answer
  1. CosmogHong-MSFT 19,906 Reputation points Microsoft Vendor
    2023-07-13T02:53:32.6933333+00:00

    Hi @arkiboys

    Try this:

    SELECT "LoginId", "Timestamp", "Update"
    FROM (
          SELECT *
                ,ROW_NUMBER() OVER (partition BY "LoginId" ORDER BY "Timestamp" DESC) AS RNum
          FROM audit."tblHistory"
         ) A
    WHERE A.RNum = 1 and "Update" = 'insert'
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Viorel 109.8K Reputation points
    2023-07-12T19:52:20.46+00:00

    Try removing the "Timestamp" from partition by.

    The query can be probably simplified.

    1 person found this answer helpful.
    0 comments No comments