Help with nested select

  • 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

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Hi Phil

    Thanks that worked.

    Sam

  • 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

  • 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

  • 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.

  • 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/

  • 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.

  • Offshore developers...whaddya gonna do? :Whistling:

    Sigerson

    "No pressure, no diamonds." - Thomas Carlyle

  • 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:-

    web designing in Agra

    Delta Technologies

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply