CODEWITHSUNDEEP
sqlsql-servert-sql
DotnetSparrow
DotnetSparrow

Reputation: 27996

Getting unique records for each group using sql

I have an sql Log table with two columns like this:

RegistrantID   compnayID  Creation date

1                1          .....
1                1             
2                1
3                1
1                2
2                2
2                2
3                2          .....

I have a stored procedure which brings first record based creation date. It brings one record if two companies has same registrant id so for registrantid 1, It will being first row and not the 5th row, and for registrantId 2, it will bring row 3 but not 6th. I want to get row 1 and 5 for registrantid 1 and 3rd and 6th for registrantid 2, row 4 and 8 for registrant id 3. Here is my stored procedure.

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

WITH CTE AS 
(
    SELECT AL.*,
        RV.Forename,
        RV.Surname,
        RV.Username AS RegistrantUsername,
        E.Forename AS EmployeeForename,
        E.Surname AS EmployeeSurname,
        U.Username,
        CASE
            WHEN @Language = 2 THEN C.NameLang2
            ELSE C.NameLang1
        END AS CompanyName,
        CASE
            WHEN @Language = 2 THEN V.JobTitleLang2
            ELSE V.JobTitleLang1
        END AS JobTitle
        , ROW_NUMBER() OVER(PARTITION BY AL.RegistrantID
                       ORDER BY ActionDate ASC) AS RN
    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
    )

    SELECT *

FROM CTE
WHERE RN = 1;

END

Please suggest how to change this stored procedure ?

Upvotes: 0

Views: 106

Answers (2)

Anup Agrawal
Anup Agrawal

Reputation: 6669

You need to partition by both RegistrantId and CompanyID

ROW_NUMBER() OVER(PARTITION BY AL.RegistrantID, AL.CompanyID 
                  ORDER BY ActionDate ASC)

Upvotes: 4

phoenixgr
phoenixgr

Reputation: 186

Removing where clause RN=1 and adding a distinct should be enough.

Upvotes: 0

Related Questions