Agfdhyk

This is my SQL script with sample data

CREATE TABLE [dbo].[Employee]

(

    [ID] [INT] IDENTITY(1,1) NOT NULL,

    [Name] [VARCHAR](100) NULL

) ON [PRIMARY]

GO



CREATE TABLE [dbo].[LoginEntry]

(

    [ID] [INT] IDENTITY(1,1) NOT NULL,

    [LoginTime] [DATETIME] NULL,

    [EmpID] [INT] NULL,

    [GateNumber] [VARCHAR](50) NULL

) ON [PRIMARY]

GO



ALTER TABLE Employee 

    ADD CONSTRAINT Pk_Employee PRIMARY KEY (Id)

GO



ALTER TABLE LoginEntry 

    ADD CONSTRAINT Fk_LoginEntry_Employee 

        FOREIGN KEY (EmpId) REFERENCES Employee(Id)

GO



SET IDENTITY_INSERT [dbo].[Employee] ON 

GO



INSERT [dbo].[Employee] ([ID], [Name]) 

VALUES (1, N'Employee 1'), (2, N'Employee 2'), (3, N'Employee 3'),

       (4, N'Employee 4'), (5, N'Employee 5'), (6, N'Employee 6')

GO



SET IDENTITY_INSERT [dbo].[Employee] OFF

GO





SET IDENTITY_INSERT [dbo].[LoginEntry] ON 

GO



INSERT [dbo].[LoginEntry] ([ID], [LoginTime], [EmpID], [GateNumber]) 

VALUES (1, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 1, N'Gate 1'),

       (2, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 1, N'Gate 1'),

       (3, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 1, N'Gate 2'),

       (4, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 2, N'Gate 1'),

       (5, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 2, N'Gate 1'),

       (6, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 2, N'Gate 2'),

       (7, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 3, N'Gate 1'),

       (8, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 3, N'Gate 1'),

       (9, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 3, N'Gate 2'),

       (10, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 4, N'Gate 1'),

       (11, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 4, N'Gate 1'),

       (19, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 5, N'Gate 1'),

       (20, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 5, N'Gate 1'),

       (21, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 5, N'Gate 2'),

       (22, CAST(N'2014-10-24 08:00:00.000' AS DateTime), 6, N'Gate 1'),

       (23, CAST(N'2014-10-24 09:00:00.000' AS DateTime), 6, N'Gate 1'),

       (24, CAST(N'2014-10-24 10:00:00.000' AS DateTime), 6, N'Gate 2')



SET IDENTITY_INSERT [dbo].[LoginEntry] OFF

GO





SELECT 

    e.ID, dt.EmpId, Name, LoginTime

FROM 

    Employee e

CROSS APPLY 

    (SELECT TOP 1

         l.ID, l.LoginTime, l.EmpId

     FROM 

         LoginEntry l 

     WHERE 

         l.EmpId = e.id) dt

GO

The result I get:

ID  EmpId   Name            LoginTime

-----------------------------------------------

1   1       Employee 1  2014-10-24 08:00:00.000

2   2       Employee 2  2014-10-24 08:00:00.000

3   3       Employee 3  2014-10-24 08:00:00.000

4   4       Employee 4  2014-10-24 08:00:00.000

5   5       Employee 5  2014-10-24 08:00:00.000

6   6       Employee 6  2014-10-24 08:00:00.000

I am Expecting the same result using Joins(inner,right,left,full) in sql server i tried my luck but couldn't, pls can any one help me out thanks in advance

First, your query is incomplete. When you use TOP 1 without an ORDER BY you never have a guarantee which one it will pick. New data, concurrent processes, re-indexing, software patches, the time of day, they all can cause the result to change.

So, it should be something like...

SELECT

  e.ID,dt.EmpId,Name,LoginTime

FROM

  Employee e

CROSS APPLY

(

  SELECT TOP 1

    l.ID

   ,l.LoginTime

   ,l.EmpId

  FROM

    LoginEntry l

  WHERE

    l.EmpId=e.id

  ORDER BY

    l.LoginTime DESC   -- Will cause TOP 1 to pick the most recent value (per employee)

)

  dt

As for doing it with joins, doing the TOP 1 (or greatest-n-per-group, for which your n is 1), is longer, messier, and slower. So I won't go in to that.

But you can use ROW_NUMBER() to do the TOP 1 part, and then use a JOIN to associate that result with your main table...

WITH

  ordered_logins AS

(

  SELECT

    *,

    ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY LoginTime DESC, ID DESC) AS row_ordinal

  FROM

    LoginEntry

)

SELECT

  e.ID, l.EmpId, e.Name, l.LoginTime

FROM

  Employee e

LEFT JOIN

  ordered_logins l

    ON  l.EmpID = e.ID

    AND l.row_oridnal = 1

The ROW_NUMBER() assigns every row a value from 1 upwards (per EmpID - the partition clause). It's order by loginTime descending, so the latest logins are first, and just in case two logins have the exact same time, it's secondarily ordered by ID desc.

Then the LEFT JOIN only picks the rows numbered 1 (the latest logins) and if there are no logins gives NULLs instead (so that the employee record is Not discarded due to a lack of join).

Note: the LEFT JOIN equivalent for APPLY is to use OUTER APPLY instead of CROSS APPLY.

Just for the sake of showing one more way to solve the problem, the "longer, messier, and slower" JOIN method that (I thought) MatBailie didn't show would look like this:

SELECT TOP (1) WITH TIES

  e.ID

 ,l.EmpID

 ,e.Name

 ,l.LoginTime

FROM

  dbo.Employee AS e

  JOIN

  dbo.LoginEntry AS l

    ON 

      l.EmpID = e.ID

ORDER BY 

  ROW_NUMBER() OVER (PARTITION BY e.ID ORDER BY l.LoginTime DESC, ID DESC)

The ROW_NUMBER in the ORDER BY clause takes all of the logins for each Employee ID and numbers them by LoginTime with the most recent first (and using the LoginEntry ID as tie breaker, and thanks for that touch, Mat).

Then, the SELECT TOP (1) WITH TIES does its thing. The WITH TIES bit means that it selects the number one result from each PARTITION BY group in the ORDER BY clause.

you can use window function row_number()

    with cte as(    

SELECT e.ID,l.EmpId,Name,l.LoginTime, row_number() over(partition by e.ID order by l.LoginTime) as rn

FROM Employee e join LoginEntry l  on l.EmpId=e.id

    ) select ID,EmpId,Name,LoginTime from cte where rn=1

demo link