Post reply

select latest records

  • January 21, 2014 at 6:12 am

    #282490

    Hi,

    I am having table which has no primary key.

    eg. tablename = tblCustomer

    columns firstnale,lastname,dateadded.

    here for single customer multiple entries are there. Now I want to select each customer single time with max dateadded.

    How can i do this?

    Thanks

    Abhas.

  • January 21, 2014 at 6:17 am

    #1681853

    Use ROW_NUMBER().

    If you can knock up a CREATE TABLE and a few INSERTs, someone will show you how.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 21, 2014 at 6:18 am

    #1681855

    create a new table with the appropriate primary key or unique constraint.

    insert into that new table grouping by the the same appropriate primary key or unique constraint criteria from the step above.

    since the original table did not have any primary key, i guess you don't have to worry about real foreign keys, but if there were any implied foreign keys, you'd want to update related data to point to the new table.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 21, 2014 at 6:21 am

    #1681856

    Hi,

    You can simply do a group by statement, unless I have misunderstood your request. See below example -

    SELECT FirstName, LastName, MAX(DateCreated)

    FROM dbo.Customer

    GROUP BY FirstName, LastName

  • January 21, 2014 at 6:25 am

    #1681860

    Thanks All,

    I am doing the same.

    SELECT

    ROW_NUMBER() OVER (PARTITION BY t.CustomerName ORDER BY t.DateAdded DESC) AS RowNumber

    from tblCustomer. but it is giving 1,2,3.....RowNumber.

    I want again repeat RowNumber to each CustomerName.

    Thanks

    Abhas.

  • January 21, 2014 at 6:43 am

    #1681870

    Thanks nicol,

    very pretty solution. :).

    Thanks

    Abhas.

  • January 21, 2014 at 6:48 am

    #1681872

    abhas (1/21/2014)

    Thanks All,

    I am doing the same.

    SELECT

    ROW_NUMBER() OVER (PARTITION BY t.CustomerName ORDER BY t.DateAdded DESC) AS RowNumber

    from tblCustomer. but it is giving 1,2,3.....RowNumber.

    I want again repeat RowNumber to each CustomerName.

    Thanks

    Abhas.

    This code snippet looks correct - can you post the whole query? There may be something not visible in this context.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • January 21, 2014 at 7:43 am

    #1681912

    I've got a number of examples on how to "get latest" rows in this article on versioned data.[/url]

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 21, 2014 at 7:44 am

    #1681916

    But, question, why no primary key? The vast majority of tables absolutely should have a primary key. Just as the vast majority of tables should have a clustered index (and they don't need to be the same column(s)).

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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