select a distinct items all order by datetime

  • Hi all

    I had a table columns with

    sno Name Case Datetime Team Id

    -------------------------------------------------------------

    1 ravikira 1122 2011-01-26 11:45:00.000 2

    2 kiran 2244 2011-02-03 00:00:00.000 3

    3 ravikira 1122 2011-01-25 00:00:00.000 3

    4 kiran 2244 2011-04-05 00:00:00.000 1

    I want output like

    sno Name Case Dateime TeamId

    ---------------------------------------------------------------

    1 ravikira 1122 2011-01-26 11:45:00.000 2

    2 kiran 2244 2011-04-05 00:00:00.000 1

    I need to remove duplicate values from the name columns and recent entry of Datetime

    Thanks

    Ravi

  • I believe you could just group the information:

    select sno, name, case, max(datetime) as MaxDate

    from tablename

    group by sno, name, case

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • Sharon Kumar (3/3/2011)


    Hi all

    I had a table columns with

    sno Name Case Datetime Team Id

    -------------------------------------------------------------

    1 ravikira 1122 2011-01-26 11:45:00.000 2

    2 kiran 2244 2011-02-03 00:00:00.000 3

    3 ravikira 1122 2011-01-25 00:00:00.000 3

    4 kiran 2244 2011-04-05 00:00:00.000 1

    I want output like

    sno Name Case Dateime TeamId

    ---------------------------------------------------------------

    1 ravikira 1122 2011-01-26 11:45:00.000 2

    2 kiran 2244 2011-04-05 00:00:00.000 1

    I need to remove duplicate values from the name columns and recent entry of Datetime

    Thanks

    Ravi

    There is no duplicate data here. Your desired output does not match the table data. You have sno 2 Kiran and TeamID 1. That just doesn't match the original data.

    If you can clarify business rules of what you are trying to do we can have a go at it.

    _______________________________________________________________

    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/

  • For my solution, I assumed Team ID was not required or that it was just a typo of the data. If my assumptions were both wrong, then yes, we need further business rules or actual sample data.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • Hi this is table values

    Investigation_IdCase_IdInvestigation_NoteDateTimeTeam_IdStatus_Id

    31101050282Testing Testing Testing 2011-01-26 11:45:00.000 2 1

    51101050282sample 2011-01-26 00:00:00.000 3 2

    91102010093fdsafsadfasd2011-02-03 00:00:00.000 2 2

    111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2

    131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1

    I need output like

    Investigation_IdCase_IdInvestigation_NoteDateTime Team_Id Status_Id

    31101050282Testing Testing 2011-01-26 11:45:00.000 2 1

    111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2

    131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1

    I need results like caseid which is recently updated.

  • Again your sample data doesn't have any duplicates. The output you displayed is exactly what's in the sample data.

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • In the caseid i have duplicated values.

  • my original SQL should work for you then.

    select Investigation_Id, Case_Id, Investigation_Note, Max(DateTime) MaxDate, Team_Id, Status_Id

    from tablename

    group by Investigation_Id, Case_Id, Investigation_Note, Team_Id, Status_Id

    --------------------------------------------------------------------------
    When you realize you've dug yourself into a hole....Step 1...stop digging.

  • Sharon Kumar (3/3/2011)


    Hi this is table values

    Investigation_IdCase_IdInvestigation_NoteDateTimeTeam_IdStatus_Id

    31101050282Testing Testing Testing 2011-01-26 11:45:00.000 2 1

    51101050282sample 2011-01-26 00:00:00.000 3 2

    I need output like

    Investigation_IdCase_IdInvestigation_NoteDateTime Team_Id Status_Id

    31101050282Testing Testing 2011-01-26 11:45:00.000 2 1

    I need results like caseid which is recently updated.

    This doesn't really work. You have part of one record and part of another. The group by example will not return a single row in this case. The Status_Id changes, the Team_Id changes. What is the correct Team? Status? Do you only want the most recent record for each Case_Id? Given you sample data and the desired output it just doesn't match up.

    _______________________________________________________________

    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/

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

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