Whats the most efficient way to get related record of a group by query?

how do you define "last note" ??

anyway, try something like this:

declare @Orientations table
(
	ID int,
	FacilityID int,
	EmployeeID int
)
insert into @Orientations
values
(1, 1, 123),
(2, 1, 456),
(3, 1, 789),
(4, 2, 222),
(5, 2, 333),
(6, 3, 444);

declare @OrientationNotes table
(
	ID int,
	OrientationID int,
	Notes varchar(500)
)
insert into @OrientationNotes
values
(1, 1, 'Hello World 1'),
(2, 1, 'Hello World 2'),
(3, 3, 'Hello World 3'),
(4, 4, 'Hello World 4'),
(5, 1, 'Hello World 5'),
(6, 5, 'Hello World 6'),
(7, 5, 'Hello World 7');

;with cte as
(
	select OrientationID, Notes, row_number() over (partition by OrientationID order by ID desc) idx from @OrientationNotes
)
select
a.ID, a.FacilityID, a.EmployeeID, b.Notes
from @Orientations a
left join (select * from cte where idx = 1) b
on a.ID = b.OrientationID

Select allOpen in new window

HELLO bfuchs,
 
SELECT One.EmployeeID,One.OrientationID,Two.Notes FROM
(
      SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID
) AS One CROSS APPLY(
      SELECT TOP 1 Notes FROM OrientationNotes AS ONotes WHERE ONotes.OrientationID = One.Id ORDER BY ONotes.Id DESC
) AS Two

Vaibhav

Hi Ryan,

how do you define "last note" ??

By max ID AutoNum of OrientationNotes table.

The problem with that suggestion is as following.

I need to include this in a store proc sql and replace the existing as follows.

if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2

Select allOpen in new window

Now instead of the following

SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2

Select allOpen in new window

I need to have the last note.
Not sure how I can accomplish that with yours..

Thanks,
Ben

@Vaibhav,

I get an error "Invalid column name OrientationID", however the column does exists..

Thanks,
Ben

Hello bfuchs,

Revised

SELECT One.EmployeeID,FacilityID,Two.Notes FROM
(
      SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID
) AS One CROSS APPLY(
      SELECT TOP 1 Notes FROM OrientationNotes AS ONotes WHERE ONotes.OrientationID = One.Id ORDER BY ONotes.Id DESC
) AS Two

Vaibhav

OK

Regarding efficiency Ryan's is much better, 3 vs. 7 sec.
However I first must see which is accurate.
Ryan gives me much more records than Vaibhav..
I changed Ryan's left join for an inner join but still see a difference in results.
Can someone help figure out what's the diff?
Also if I'm to apply Ryan's suggestion (due to efficiency), how can I have that encapsulated in another SQL statement?

Thanks,
Ben

@Vaibhav,

It doesn't look like the logic is correct..
I need the last note per employee, and from there to view the related orientation fields.
While the following gives me the last Orientation per employee

SELECT EmployeeID,FacilityID,MAX(Id) Id FROM Orientations GROUP BY EmployeeID,FacilityID

Select allOpen in new window

Thanks,
Ben

Hello Ben

Impressive.

Thank you very much for the feedback. I request you provide some sample data from the tables and the output you are expecting

Vaibhav

if @strRetColums is not null
      set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM        
dbo.OrientationNotes o2 ' + @strWhere1 +
'  ) SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM        
dbo.OrientationNotes o2

i think the sp script posted above is not complete so can't really give you a direct answer.

but it seems that you got some info from other tables, such as Employeestbl and Facilitiestbl, but to make it simple, you can always join your tables together using CTE syntax, to make your SQL statement looks easier and more easy to understand.

@Ryan,

This is the complete stored proc.

USE [PlacementNP]
GO
/****** Object:  StoredProcedure [dbo].[proc_FilterOrientationNotes1]    Script Date: 12/19/2017 13:16:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[proc_FilterOrientationNotes1]
(@strWhere varchar(5000),
 @strRetColums varchar(500) = null

 )
AS 
declare @tmp varchar(5000)
declare @strWhere1 varchar(5000)
declare @strWhere2 varchar(5000)

set @strWhere1 = (select [value] from ProgrammingSettings where [Code] = 'UniqueFilterOrientationNotesSql')

if @strRetColums is null

	set @tmp='SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, dbo.Employeestbl.AddressLine1,
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + '' '' + dbo.Employeestbl.FirstName As EmpName'
if @strRetColums is not null
	set @tmp = 'Select ' + @strRetColums
set @tmp = @tmp + ' FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID'


IF @strWhere IS not NULL and @strwhere <> 'ALL' and @strwhere <> 'Long' set @tmp = @tmp + ' where ' + @strWhere 

IF @strWhere IS NULL set @tmp = @tmp + ' where Orientations.EmployeeID = 3' 

if @strWhere = 'Long'
begin
	set @strWhere2 = (select [value] from ProgrammingSettings where [Code] = 'FilterOrientationNotesSql')
	set @tmp = @tmp + ' where ' + @strWhere2 
end



	--if charindex('order by',@strWhere)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
	if charindex('order by',@tmp)= 0 set @tmp = @tmp + ' ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc'
execute (@tmp)

Select allOpen in new window

As mentioned, I'm trying to replace the following code which gives me the last note per orientation (passing to @strWhere1  criteria + GroupBy OrientationID)

 LEFT OUTER JOIN
(SELECT     OrientationID,  MAX(o2.ID) AS LastNoteID
FROM         
dbo.OrientationNotes o2 ' + @strWhere1 + 
'  ) note ON dbo.Orientations.ID = note.OrientationID

Select allOpen in new window

With what you had suggested, which should give me the last note per employee.

Thanks,
Ben

@Vaibhav,

Sorry I didnt meant to offend, just was testing for accurancy.

For creating test scenario, we can use what Pyan posted above,

declare @Orientations table
(
	ID int,
	FacilityID int,
	EmployeeID int
)
insert into @Orientations
values
(1, 1, 123),
(2, 1, 456),
(3, 1, 789),
(4, 2, 222),
(5, 2, 333),
(6, 3, 444);

declare @OrientationNotes table
(
	ID int,
	OrientationID int,
	Notes varchar(500)
)
insert into @OrientationNotes
values
(1, 1, 'Hello World 1'),
(2, 1, 'Hello World 2'),
(3, 3, 'Hello World 3'),
(4, 4, 'Hello World 4'),
(5, 1, 'Hello World 5'),
(6, 5, 'Hello World 6'),
(7, 5, 'Hello World 7');

Select allOpen in new window

Thanks,
Ben

Hi,

First thanks for taking the effort helping me out with this..

When I run I get printed the following

--execute [proc_FilterOrientationNotes122] 'long'

;with o2 as
(
	select OrientationID, Notes, row_number() over (partition by OrientationID order by ID desc) idx from OrientationNotes
)
SELECT     TOP (99.9999) PERCENT CAST(CASE WHEN Last_Orient_Note_Day IS NULL OR
                      Last_Orient_Note_Day < GETDATE() - 60 THEN 1 ELSE 0 END AS bit) AS Red, dbo.OrientationNotes.ID, dbo.Orientations.EmployeeID, 
                      dbo.Orientations.FacilityID, dbo.Orientations.Active, dbo.OrientationNotes.Note, COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) 
                      AS Day, dbo.OrientationNotes.Initial, dbo.Orientations.ID AS OrientationID, dbo.Orientations.DateEntered, dbo.Orientations.Initial AS OrientationInitial, 
                      dbo.Orientations.Traveler, dbo.EmployeesDocsTov.TovInfoLastDay AS LastDay, dbo.Orientations.DueDate, dbo.OrientationNotes.Mailing, 
                      dbo.Orientations.DueDateInitial, dbo.EmployeesDocsTov.Last_Orient_Note_Day, dbo.Employeestbl.AvailibilityPDays, 
                      dbo.Employeestbl.AvailibilityPShifts, dbo.Employeestbl.City, dbo.Employeestbl.LastName, dbo.Employeestbl.FirstName, dbo.Employeestbl.Title, dbo.Employeestbl.AddressLine1,
                      dbo.Facilitiestbl.Name AS FacilityName, note.LastNoteID, dbo.EmployeesDocsTov.TovInfoFacility1 AS LastFacility, 
                      dbo.Employeestbl.Degree, dbo.Employeestbl.NotSendMassTextMsg, dbo.Employeestbl.NotSendMassEmail, dbo.Employeestbl.Email, 
                      dbo.Employeestbl.Beeper, dbo.Employeestbl.SMSProvider, dbo.Employeestbl.ExpectedGraduation,
					  dbo.Employeestbl.LastName + ' ' + dbo.Employeestbl.FirstName As EmpName FROM         dbo.Orientations INNER JOIN
                      dbo.Employeestbl ON dbo.Orientations.EmployeeID = dbo.Employeestbl.ID INNER JOIN
                      dbo.Facilitiestbl ON dbo.Orientations.FacilityID = dbo.Facilitiestbl.ID LEFT OUTER JOIN
(SELECT     o2.OrientationID,  o2.ID AS LastNoteID
FROM         
 o2  Group BY OrientationID  and o2.idx = 1   ) note ON dbo.Orientations.ID = note.OrientationID
                       LEFT OUTER JOIN
                      dbo.EmployeesDocsTov ON dbo.Orientations.EmployeeID = dbo.EmployeesDocsTov.EmployeeID LEFT OUTER JOIN
                      dbo.OrientationNotes ON dbo.Orientations.ID = dbo.OrientationNotes.OrientationID where  COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) >= '01/01/2006' and COALESCE (dbo.OrientationNotes.Day, dbo.Orientations.DateEntered) >= '12/19/2017' And OrientationNotes.Initial = 'RO' And Orientations.EmployeeID in (select ID from [EmployeesTbl] where isnull(EmployeeStatus,'') <> 'Disapproved') ORDER BY Orientations.DateEntered DESC, OrientationNotes.ID desc

Select allOpen in new window

And this gives me an error "Incorrect syntax near the keyword 'and'." at following line.

 o2  Group BY OrientationID  and o2.idx = 1   ) note ON dbo.Orientations.ID = note.OrientationID

Select allOpen in new window

Thanks,
Ben

ok... so what you retrieved from the db (table: ProgrammingSettings) contains "Group BY OrientationID " instead of a where condition.

since we're using another approach, can you empty the value from ProgrammingSettings where Code = 'UniqueFilterOrientationNotesSql'?

which means we no longer need a Group By here.

Hi Ryan,

Sorry for the delay, hope to get to the office later on today & test this..

can you empty the value from ProgrammingSettings where Code = 'UniqueFilterOrientationNotesSql'?

I also use that to send along a where clause, for example user filtered for notes within a date range, so that inner query should only contain notes filtered for.

Thanks,
Ben

I also use that to send along a where clause, for example user filtered for notes within a date range, so that inner query should only contain notes filtered for.

ok, but in the case above, do you still want to use "Group BY OrientationID" ? I think it's no longer necessary here...

Thank you very much Ryan!