April 29, 2015 at 5:47 am
Hi
I have a temp table shown as the following.
CREATE TABLE #Table1 (ID INT IDENTITY(1,1), Person int, UpdatedDate DATETIME)
I then insert data.
INSERT INTO #Table1
VALUES (1, GETDATE()),(1, GETDATE() - 1),(2, GETDATE()), (2, NULL)
IDPersonUpdatedDate
112015-04-29 12:26:06.040
212015-04-28 12:26:06.040
322015-04-29 12:26:06.040
42NULL
I want to get the entries with the latest UpdatedDate per Person, if the UpdatedDate is null make it GETDATE().
The following query does this.
SELECT * FROM #Table1 t1
WHERE ID IN (SELECT TOP 1 ID FROM #Table1 t2
WHERE t1.Person = t2.Person
ORDER BY ISNULL(UpdatedDate, GETDATE()) DESC)
however is there a more efficient way?
This is not an issue with this temp table, however in the production view I am looking at it takes 23 minutes to bring back 54,000 rows.
Thanks
Sam
April 29, 2015 at 6:06 am
Try this
select t.Person
,UpdatedDate = max(isnull(t.UpdatedDate, getdate()))
from #Table1 t
group by t.Person;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 29, 2015 at 9:22 am
Hi Phil
Thanks that worked.
Sam
May 1, 2015 at 9:20 am
I have another similar one if anyone can help.
CREATE TABLE #Courses (RandomID INT, PersonID INT, CourseTitle NVARCHAR(200), CourseDate DATETIME)
INSERT INTO #Courses
VALUES
(1,1,'Maths','2015-05-01 15:30'),
(2,1,'Science','2015-05-01 15:30'),
(3,1,'Science','2015-05-01 15:30'),
(4,1,'Science','2015-06-01 15:30'),
(5,1,'Maths','2015-04-01 15:30'),
(6,2,'Science','2015-05-01 15:30'),
(7,3,'Science','2015-05-01 15:30'),
(8,4,'Science','2015-05-01 15:30'),
(9,5,'Science','2015-05-01 15:30'),
(10,6,'Science','2015-05-01 15:30'),
(11,7,'Science','2015-05-01 15:30'),
(12,1,'Science','2015-06-01 15:30')
I would like to get 1 entry for each PersonID and CourseTitle but I want the latest date.
So for PersonID 1 I would like to get back row 1 and 4.
The query below works but on the production view that has been set up it takes a very long time to bring back not very much.
SELECT * FROM #Courses C
WHERE RandomID = (
SELECT TOP 1 RandomID FROM #Courses C1
WHERE C.CourseTitle = C1.CourseTitle
AND C.PersonID = C1.PersonID
ORDER BY CourseDate DESC)
Is there a better way to do this?
I thought with a CTE but I tried the following
WITH Course AS (
SELECT PersonID, CourseTitle, MAX(CourseDate) AS CourseDate
FROM #Courses
GROUP BY PersonID, CourseTitle
)
SELECT * FROM #Courses C
JOIN Course C1
ON C.PersonID = C1.PersonID
AND C.CourseTitle = C1.CourseTitle
AND C.CourseDate = C1.CourseDate
It brings back row 4 and 12.
Thanks
Sam
May 1, 2015 at 9:33 am
Sam Garth (5/1/2015)
I have another similar one if anyone can help.CREATE TABLE #Courses (RandomID INT, PersonID INT, CourseTitle NVARCHAR(200), CourseDate DATETIME)
INSERT INTO #Courses
VALUES
(1,1,'Maths','2015-05-01 15:30'),
(2,1,'Science','2015-05-01 15:30'),
(3,1,'Science','2015-05-01 15:30'),
(4,1,'Science','2015-06-01 15:30'),
(5,1,'Maths','2015-04-01 15:30'),
(6,2,'Science','2015-05-01 15:30'),
(7,3,'Science','2015-05-01 15:30'),
(8,4,'Science','2015-05-01 15:30'),
(9,5,'Science','2015-05-01 15:30'),
(10,6,'Science','2015-05-01 15:30'),
(11,7,'Science','2015-05-01 15:30'),
(12,1,'Science','2015-06-01 15:30')
I would like to get 1 entry for each PersonID and CourseTitle but I want the latest date.
So for PersonID 1 I would like to get back row 1 and 4.
The query below works but on the production view that has been set up it takes a very long time to bring back not very much.
SELECT * FROM #Courses C
WHERE RandomID = (
SELECT TOP 1 RandomID FROM #Courses C1
WHERE C.CourseTitle = C1.CourseTitle
AND C.PersonID = C1.PersonID
ORDER BY CourseDate DESC)
Is there a better way to do this?
I thought with a CTE but I tried the following
WITH Course AS (
SELECT PersonID, CourseTitle, MAX(CourseDate) AS CourseDate
FROM #Courses
GROUP BY PersonID, CourseTitle
)
SELECT * FROM #Courses C
JOIN Course C1
ON C.PersonID = C1.PersonID
AND C.CourseTitle = C1.CourseTitle
AND C.CourseDate = C1.CourseDate
It brings back row 4 and 12.
Thanks
Sam
does this get you on the right road?
edit....would be better to post as a new thread
WITH CTE as (
SELECT
PersonID
, CourseTitle
, CourseDate
, row_number() over (PARTITION BY PersonID, CourseTitle ORDER BY CourseDate Desc) rn
FROM Courses
)
SELECT
PersonID
, CourseTitle
, CourseDate
FROM CTE
WHERE rn = 1
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 1, 2015 at 9:41 am
Hi
I amended your idea and came up with the following.
WITH Course
AS ( SELECT RandomId,
PersonID,
CourseTitle,
CourseDate,
ROW_NUMBER() OVER( PARTITION BY PersonID,
CourseTitle ORDER BY CourseDate DESC ) rn
FROM #Courses )
SELECT *
FROM course
WHERE rn = 1;
and that seems to do the trick.
Thank you.
May 1, 2015 at 9:42 am
Sam Garth (5/1/2015)
I have another similar one if anyone can help.CREATE TABLE #Courses (RandomID INT, PersonID INT, CourseTitle NVARCHAR(200), CourseDate DATETIME)
INSERT INTO #Courses
VALUES
(1,1,'Maths','2015-05-01 15:30'),
(2,1,'Science','2015-05-01 15:30'),
(3,1,'Science','2015-05-01 15:30'),
(4,1,'Science','2015-06-01 15:30'),
(5,1,'Maths','2015-04-01 15:30'),
(6,2,'Science','2015-05-01 15:30'),
(7,3,'Science','2015-05-01 15:30'),
(8,4,'Science','2015-05-01 15:30'),
(9,5,'Science','2015-05-01 15:30'),
(10,6,'Science','2015-05-01 15:30'),
(11,7,'Science','2015-05-01 15:30'),
(12,1,'Science','2015-06-01 15:30')
I would like to get 1 entry for each PersonID and CourseTitle but I want the latest date.
So for PersonID 1 I would like to get back row 1 and 4.
The query below works but on the production view that has been set up it takes a very long time to bring back not very much.
SELECT * FROM #Courses C
WHERE RandomID = (
SELECT TOP 1 RandomID FROM #Courses C1
WHERE C.CourseTitle = C1.CourseTitle
AND C.PersonID = C1.PersonID
ORDER BY CourseDate DESC)
Is there a better way to do this?
I thought with a CTE but I tried the following
WITH Course AS (
SELECT PersonID, CourseTitle, MAX(CourseDate) AS CourseDate
FROM #Courses
GROUP BY PersonID, CourseTitle
)
SELECT * FROM #Courses C
JOIN Course C1
ON C.PersonID = C1.PersonID
AND C.CourseTitle = C1.CourseTitle
AND C.CourseDate = C1.CourseDate
It brings back row 4 and 12.
Thanks
Sam
Look at your sample data. Of course you get rows 4 and 12 because they are identical. They both have the same course for the same person on the same date.
Try something like this.
with MySortedData as
(
SELECT PersonID
, CourseTitle
, CourseDate
, ROW_NUMBER() over (partition by PersonID, CourseTitle order by CourseDate desc) as RowNum
FROM #Courses
)
select *
from MySortedData
where RowNum = 1
I know this is sample data but it would help you immensely if you normalized your data.
--EDIT--
I got pulled away while posting and it seems there have been a couple new posts with pretty much the same thing I posted. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2015 at 9:46 am
Thanks for all your help. The issue is that it is using a view that pulls from multiple tables and it does bring back some duplicates.
I am not even sure if that is what was originally intended but it was created by a team in Singapore and I am not getting any help from them so I can't make any changes to what it brings back.
May 1, 2015 at 11:08 am
Offshore developers...whaddya gonna do? :Whistling:
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 2, 2015 at 5:21 am
You should try this, A simplest way to resolve your query
Select t1.Person, max(isnull(t1.UpdatedDate, getdate())) from Table1 t1 group by t1.Person;
using "having" clause to filter the data
Thanks By:-
Delta Technologies
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply