Selecting Top 1 of latest data from joined query

Question

this is my query

SELECT  u.UserID ,
        A.Username ,
        A.Password ,
        A.CreatedOn
FROM    dbo.tbl_User U
        INNER JOIN dbo.tbl_UserAuthDetail A ON A.UserID = U.UserID
                                               AND A.CreatedOn IN (
                                               SELECT TOP 1
                                                        CreatedOn
                                               FROM     dbo.tbl_UserAuthDetail
                                               WHERE    A.UserID = U.UserID
                                               ORDER BY CreatedOn DESC )

and it will return

but i have this on my table

so i am expecting that there is additional entry to result

can someone guide me if where did i go wrong? and if possible, help me also converting that to LINQ..

Sorry for my bad explaination.. :)

TIA

Answer 1

Something is fishy with your subquery's where statement:

FROM     dbo.tbl_UserAuthDetail
WHERE    A.UserID = U.UserID

Both A and U are outside the subquery, so this clause will not limit the subquery at all.

Try something like:

FROM     dbo.tbl_UserAuthDetail A2
WHERE    A2.UserID = U.UserID

---

As an alternative solution, SQL Server has window functions:

select  *
from    (
        select  row_number() over (
                    partition by u.UserID
                    order by a.createdon desc) rn
        ,       *
        from    dbo.tbl_user u
        join    dbo.tbl_userauthdetail a
        on      a.userid = u.userid
        ) as SubQueryAlias
 where  rn = 1 -- Only latest authdetail per user

Or you can use a cross apply:

select  *
from    dbo.tbl_user u
cross apply
        (
        select  top 1 *
        from    dbo.tbl_userauthdetail a
        where   a.userid = u.userid
        order by
                a.createdon desc
        ) a

Answer 2

Try using:

select u.userid, a.username, a.password, a.createdon
  from dbo.tbl_user u
  join dbo.tbl_userauthdetail a
    on a.userid = u.userid
 where a.createdon = (select max(x.createdon)
                        from dbo.tbl_UserAuthDetail x
                       where x.userid = a.userid)

In your query you joined A with U.

You should have joined dbo.tbl_UserAuthDetail with A.

The above does that, although I gave dbo.tbl_UserAuthDetail an alias of X.

I also moved the subquery into the WHERE clause since it is not a join condition.

I also selected the max(createdon) which is functionally equivalent to using 'top 1 order by createdon desc' however it is more universal sql.