selecting distinct data using group by in SQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

selecting distinct data using group by in SQL

Author

Topic

sparrow37
Posting Yak Master

148 Posts

Posted - 2013-08-04 : 08:01:39

Hi all:

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 ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-04 : 10:15:34

[code]
SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY CompanyID ORDER BY ActionDate) AS Seq
FROM
)t
WHERE Seq=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources