Help Needed in Top selection from the list

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  March 10, 2016 at 1:11 pm

  #399840

  Hi,

  Sample data to play with

  Declare @Company table(CompId int,CompanyName varchar(30));

  Declare @UserVisit table (UserId int,CompId int, VisitedDate datetime);

  Insert into @Company select 1, 'Microsoft' union all select 2,'IBM' union all select 3,'Oracle';

  Insert into @UserVisit select 100,1,'2016-02-10' union all

  select 100,1,'2016-02-01' union all

  select 1000,2,'2016-01-15' union all

  select 1000,2,'2016-01-10' union all

  select 2000,3,'2015-12-30' union all

  select 2000,3,'2012-12-04'

  Expected result:

  select 100 as UserId ,'Microsoft' as CompanyName,'2016-02-10' as lastvisited union all

  select 1000,'IBM','2016-01-15' union all

  select 1001,'Oracle','2015-12-30'

  This is not my actual column name and actual table structure.I build this data to play with. Basically, i would like to know how to get the latest visited records by CompId.

  Any sample query to show how to achieve this please

  Thanks

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 10, 2016 at 1:22 pm

  #1864390

  Use a CTE with ROW_NUMBER to find the first for each group.

  WITH CTE AS(

  SELECT c.CompanyName,

  v.UserId,

  v.VisitedDate,

  ROW_NUMBER() OVER(PARTITION BY c.CompId ORDER BY v.VisitedDate DESC) rn

  FROM @Company c

  JOIN @UserVisit v ON c.CompId = v.CompId

  )

  SELECT UserId,

  CompanyName,

  VisitedDate

  FROM CTE

  WHERE rn = 1;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• djj

  SSCoach

  Points: 18831

  More actions

  March 10, 2016 at 1:22 pm

  #1864391

  KGJ-Dev (3/10/2016)

  ---

  Hi,

  Sample data to play with

  Declare @Company table(CompId int,CompanyName varchar(30));

  Declare @UserVisit table (UserId int,CompId int, VisitedDate datetime);

  Insert into @Company select 1, 'Microsoft' union all select 2,'IBM' union all select 3,'Oracle';

  Insert into @UserVisit select 100,1,'2016-02-10' union all

  select 100,1,'2016-02-01' union all

  select 1000,2,'2016-01-15' union all

  select 1000,2,'2016-01-10' union all

  select 2000,3,'2015-12-30' union all

  select 2000,3,'2012-12-04'

  Expected result:

  select 100 as UserId ,'Microsoft' as CompanyName,'2016-02-10' as lastvisited union all

  select 1000,'IBM','2016-01-15' union all

  select 1001,'Oracle','2015-12-30'

  This is not my actual column name and actual table structure.I build this data to play with. Basically, i would like to know how to get the latest visited records by CompId.

  Any sample query to show how to achieve this please

  Thanks

  How about this

  SELECT C.UserId, C.CompanyName, MAX(U.VisitedDate) lastvisited

  FROM @Company C

  INNER JOIN @UserVisit U ON C.UserID = U.UserID

  GROUP BY C.UserId, C.CompanyName;

• KGJ-Dev

  SSCertifiable

  Points: 5780

  More actions

  March 10, 2016 at 1:52 pm

  #1864400

  Thank you champs and i tried with cross apply to and it's working. Nice to learn three ways to achieve this. Appreciated your time.