Obtain Separate Data Using SQL (Top 1)

advertisements

I have a stored procedure which gets data from different tables using LEFT join. I want to pick distinct records ( first one only).

Stored procedure is like this:

ALTER PROCEDURE [dbo].[hr_ActionLog_GetList]

    @Action INT = NULL,

    @DateFrom DATETIME = NULL,

    @DateTo DATETIME = NULL,

    @CompanyID INT = NULL,

    @RegistrantID INT = NULL,

    @VacancyID INT = NULL,

    @Language INT = 1

AS

BEGIN

    SELECT AL.[RegistrantID]
  ,[EmployeeID]
  ,AL.[UserID]
  ,[CompanyID]
  ,[VacancyID]
  ,[Action])
  ,[ActionDate],

        RV.Forename,

        RV.Surname,

        RV.Username AS RegistrantUsername,

        E.Forename AS EmployeeForename,

        E.Surname AS EmployeeSurname,

        U.Username,

        CASE

            WHEN @Language = 2 THEN V.JobTitleLang2

            ELSE V.JobTitleLang1

        END AS JobTitle

    FROM dbo.hr_ActionLog AL LEFT OUTER JOIN dbo.RegistrantsListView RV ON AL.RegistrantID = RV.RegistrantID 

        LEFT OUTER JOIN dbo.hr_Employees E ON AL.EmployeeID = E.EmployeeID

        LEFT OUTER JOIN dbo.hr_Users U ON AL.UserID = U.UserID

        LEFT OUTER JOIN dbo.hr_Companies C ON AL.CompanyID = C.CompanyID

        LEFT OUTER JOIN dbo.hr_Vacancies V ON AL.VacancyID = V.VacancyID

        LEFT OUTER JOIN dbo.hr_Companies VC ON V.CompanyID = VC.CompanyID

    WHERE (@Action IS NULL OR AL.Action = @Action)

        AND (@DateFrom IS NULL OR dbo.DateOnly(AL.ActionDate) >= dbo.DateOnly(@DateFrom))

        AND (@DateTo IS NULL OR dbo.DateOnly(AL.ActionDate) <= dbo.DateOnly(@DateTo))

        AND (@CompanyID IS NULL OR AL.CompanyID = @CompanyID)

        AND (@RegistrantID IS NULL OR AL.RegistrantID = @RegistrantID)

        AND (@VacancyID IS NULL OR AL.VacancyID = @VacancyID)

    ORDER BY AL.ActionDate DESC 

END

sample data

1786    16294   15  16321   3   NULL    4   2013-08-03 12:18:08.130 cv  3   [email protected]  asif    hameed  [email protected] my company1aa   NULL    NULL
1785    16294   15  16321   3   NULL    4   2013-08-03 12:17:57.797 cv  3   [email protected]  asif    hameed  [email protected] my company1aa   NULL    NULL
1784    16293   15  16321   3   NULL    4   2013-08-03 12:17:47.243 cv  2   [email protected]  asif    hameed  [email protected] my company1aa   NULL    NULL
1783    16295   15  16321   3   NULL    4   2013-08-03 12:17:40.967 cv  4   [email protected]  asif    hameed  [email protected] my company1aa   NULL    NULL
1782    16292   15  16321   3   NULL    4   2013-08-03 12:17:31.953 cv  1   [email protected]  asif    hameed  [email protected] my company1aa   NULL    NULL

I want to get first record from action log table which is distinct.


I am not sure whether I understood your question correct; when you say "I want top get first record which is distinct". But something like this might help. I am still not sure what columns have duplicate values, but you can try this.

Select Distinct TOP 1 *  from
( SELECT
  AL.[RegistrantID]
  ,[EmployeeID]
  ,AL.[UserID]
  ,[CompanyID]
  ,[VacancyID]
  ,[Action])
  ,[ActionDate],

        RV.Forename,

        RV.Surname,

        RV.Username AS RegistrantUsername,

        E.Forename AS EmployeeForename,

        E.Surname AS EmployeeSurname,

        U.Username,

        CASE

            WHEN @Language = 2 THEN V.JobTitleLang2

            ELSE V.JobTitleLang1

        END AS JobTitle

    FROM dbo.hr_ActionLog AL LEFT OUTER JOIN dbo.RegistrantsListView RV ON AL.RegistrantID = RV.RegistrantID 

        LEFT OUTER JOIN dbo.hr_Employees E ON AL.EmployeeID = E.EmployeeID

        LEFT OUTER JOIN dbo.hr_Users U ON AL.UserID = U.UserID

        LEFT OUTER JOIN dbo.hr_Companies C ON AL.CompanyID = C.CompanyID

        LEFT OUTER JOIN dbo.hr_Vacancies V ON AL.VacancyID = V.VacancyID

        LEFT OUTER JOIN dbo.hr_Companies VC ON V.CompanyID = VC.CompanyID

    WHERE (@Action IS NULL OR AL.Action = @Action)

        AND (@DateFrom IS NULL OR dbo.DateOnly(AL.ActionDate) >= dbo.DateOnly(@DateFrom))

        AND (@DateTo IS NULL OR dbo.DateOnly(AL.ActionDate) <= dbo.DateOnly(@DateTo))

        AND (@CompanyID IS NULL OR AL.CompanyID = @CompanyID)

        AND (@RegistrantID IS NULL OR AL.RegistrantID = @RegistrantID)

        AND (@VacancyID IS NULL OR AL.VacancyID = @VacancyID)

    ORDER BY AL.ActionDate DESC )  subquery

You can even add Distinct inside the subquery and just select TOP 1 * in the main select