dataset containing the max(date):

  • I have a table with 1 PK, and this table contains multiple records per employee.

    So, I would like to be able to get a dataset for all the employees having to show the latest record entry for each employee.

    Example:

    1, "john doe", 12345, 01/01/2010

    1, "john doe", 12345, 01/15/2010

    2, "Jack, OneEye", 3434, 01/01/2010

    1, "john does", 12345, 01/20/2010

    2, "Jack, OneEye", 3434, 01/10/2010

    needed results:

    1, "john doe", 12345, 1/20/2010

    2, "Jack, OneEye", 3434, 1/10/2010

    Hope this makes sense.

    I know that I can use the Cursor to get what I want, or even using multiple queries in conjunction to temp tables..

    but, I am looking for an atomic function or a sp if not all in TSQL.

    In another word, I like this to be done with the minimum number of times taking the water bucket to the well.

    thx for any information.

    JohnE

    Cheers,
    John Esraelo

  • Did you try the GROUP BY clause (for details please see BOL)?



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I have looked at that too, but, I have so many fields in this record structure and also there are couple of memo / text fields. So, I am not sure if the aggregation would work with this.. But the second fresh pair of eyes might help me here.. I will revisit that..

    I wonder if self-querying the same table (recursively) would work. hmmm

    Cheers,
    John Esraelo

  • You could wrap the GROUP BY into a subquery or a CTE and join that to your base table.

    I didn't know the number of columns nor do I know if you have any kind of a identifier column that could be used...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • how about the statement in below.. I did get some assistance on this paragraph. .Now, I have to find out how to use this in a table-value function.

    thx for the information guys..

    🙂

    ;with cte as

    (

    select *, row_number() over (partition by [emp id fk] order by [appdate] desc) as seqnum

    from mydb.dbo.applications

    )

    select *

    from cte where seqnum =1 and [emp id fk] = 4

    Cheers,
    John Esraelo

  • Why would you need a function?

    If you want to return all [emp id fk], simply remove "and [emp id fk] = 4" from your statement and you'll get the latest entry for all emp id's.

    What would be the purpose of that function and how would you use it? There might be easier ways to do what you want to do...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • that's a very good point and I am glad that you brought that up.

    you see, I have a

    > data warehouse

    > there is another table joining this table / dataset

    therefore, in my database dataset view of my project / solution I would like to convert my table (that is currently in my data view) into a named query that would only bring back the new dataset and eventually I will be writing that to a dim table ...

    I hope that makes sense..

    now, to answer to your question, yes, you are right I probably do not need the function and I can just grab my statement without the parameter and replace my table in the data warehouse project (data view) and call it the day..

    I will let you know..

    And, if you have other solutinos please.. by all means.. I am all ears..

    thx

    Cheers,
    John Esraelo

  • FYI

    just an update:

    I did use the statement, minus the ID filter, in my data warehouse and it failed during the creation of a named query.

    What's interesting is that it shows the returned records perfectly but it won't allow me to save it because of the either syntax or the semantics..

    keep on trying unless you folks have a better idea(s).

    thx a bunch again guys..

    Cheers,
    John Esraelo

  • If you try to save it as a view then you'd need to remove the semicolon from the beginning of the statement, since it actually is the end of the previous statement (whereas it isn't allowed to have multiple statements when declaring a view).



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • very good thinking, but, that did not work.. same error message about creating this type of query in a "named query" in data view part of the solution.

    🙁

    Cheers,
    John Esraelo

  • A couple of ways that work pretty well. First, and this will work in 2000/05/08, is to use a sub-select with the TOP and ORDER BY operation like this:

    SELECT ...

    FROM TableX x

    JOIN TableY y

    ON x.id = y.Id

    and y.Date = (SELECT TOP 1 y2.Date

    FROM TableY y2

    WHERE x.ID = y2.ID

    ORDER BY y2.Date DESC)

    A newer way to do it, although I've found that it doesn't always perform quite as well, is to use the ROWNUMBER function with ORDER BY and PARTITION

    WITH y2 AS (

    SELECT y.Id

    ,y.Date

    ,ROWNUMBER() OVER (PARTITION BY y.ID ORDER BY y.Date DESC) AS RowNum

    FROM y)

    SELECT ....

    FROM TableX x

    JOIN y2

    ON x.Id = y.ID

    and y.RowNum = 1

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Viewing 11 posts - 1 through 11 (of 11 total)

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