Select distinct - SQL Server Forums

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

Select distinct

Author

Topic

puget_bound
Starting Member

1 Post

Posted - 2014-12-30 : 19:29:05

I have a dataset with a new record created for each employee every month. The fields are Personnel #, Position Description, and Date. I want to query that dataset, getting distinct personnel #s and position descriptions, but only I only want to include the earliest date available for each record. So far it's looking like this:

select distinct top 10 [PERSONNEL #], [POSITION DESCRIPTION], date
from employees
where [personnel #] is not null
and [personnel #] <> ''
order by [personnel #]

The problem is that it includes a month for each entry, which defeats the purpose of the distinct. Any ideas on this?

Cheers

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-12-31 : 03:40:16

Try this:

select p2.[personnel #]
      ,p2.[position description]
      ,p2.[date]
  from (select [personnel #]
              ,max([date]) as [date]
          from dbo.employees
         where [personnel #] is not null
           and [personnel #]<>''
         group by [personnel #]
       ) as p1
       inner join dbo.employees as p2
               on p1.[personnel #]=p2.[personnel #]
              and p1.[date]=p2.[date]
 order by p2.[personnel #]

Or this:

select [personnel #]
      ,[personnel description]
      ,[date]
  from (select [personnel #]
              ,[personnel description]
              ,[date]
              ,row_number() over(partition by [personnel #] order by [date] desc) as rn
          from employees
         where [personnel #] is not null
           and [personnel #]<>''
       ) as p
 where rn=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources