compare sql queries

Question

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'

Answer 1

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.

Answer 2

Try removing the "Timestamp" from partition by.

The query can be probably simplified.