CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
DotnetSparrow
DotnetSparrow

Reputation: 27996

selecting distinct data using group by in SQL

I have an action log table which records when a registrant record was viewed by a compnay employee. I have an sql query like this:

SELECT [ID]
      ,[RegistrantID]
      ,[EmployeeID]
      ,[UserID]
      ,[CompanyID]
      ,[VacancyID]
      ,[Action]
      ,[ActionDate]
  FROM [Hrge].[dbo].[hr_ActionLog]
  where action = 4
  and CompanyID = 3
  order by ActionDate desc

and data is like this:

ID  RegistrantID EmployeeID UserID CompanyID VacancyID Action ActionDate
1793    16295   15  16321   3   NULL       4    2013-08-04 16:45:40.457
1792    16292   15  16321   3   NULL       4    2013-08-04 16:45:33.003
1791    NULL    15  16321   3   NULL       3    2013-08-04 16:45:23.660
1790    16295   9   16289   3   NULL       4    2013-08-04 16:45:09.543
1789    16295   9   16289   3   NULL       4    2013-08-04 16:45:00.817
1799    16295   15  16321   3   NULL       4    2012-08-04 16:45:40.457
1797    16292   15  16321   3   NULL       4    2012-08-04 16:45:33.003
1796    NULL    15  16321   3   NULL       3    2012-08-04 16:45:23.660
1795    16295   9   16289   3   NULL       4    2012-08-04 16:45:09.543
1794    16295   9   16289   3   NULL       4    2012-08-04 16:45:00.817

I want to select distinct views to a registrantid record ( the first ones) in one year. if a registrant was viewed 10 tmes a year then it will show only first time it was viewed. If it was viewed 10 times by an employeed in 2 years then it will show first time it was viewed. if it was viewed by 2 employees of same company 10 times in one year then it first time viewed record will be shown. if it was seen 10 times by 2 employees of two different companies in one year then first record of two companies will be shown. do i need to use group by or what ?

Upvotes: 1

Views: 143

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Use the ranking function ROW_NUMBER with PARTITION BY RegistrantID ORDER BY ActionDate to get the first date for each RegistrantID:

WITH CTE AS 
(
   SELECT *,
     ROW_NUMBER() OVER(PARTITION BY RegistrantID
                       ORDER BY ActionDate) AS RN
   FROM [Hrge].[dbo].[hr_ActionLog]
   WHERE action = 4
     AND CompanyID = 3
)
SELECT [ID]
      ,[RegistrantID]
      ,[EmployeeID]
      ,[UserID]
      ,[CompanyID]
      ,[VacancyID]
      ,[Action]
      ,[ActionDate]
FROM CTE
WHERE RN = 1;

Upvotes: 1

Related Questions