locked
Get Last Data In Current Date RRS feed

  • Question

  • User2033107836 posted

    Hello 

    Assume My Current Date Is : 03-Jan-2017

    My table data is like this below

    Batch No Category Date
    B1 A 1-Jan-17
    B2 A 1-Jan-17
    B3 A 1-Jan-17
    B2 B 2-Jan-17
    B5 G 3-Jan-17
    B2 M 3-Jan-17

    I want result set like this

    Batch No Category Date
    B1 A 1-Jan-17
    B3 A 1-Jan-17
    B2 M 3-Jan-17
    B5 G 3-Jan-17

    Need SQL Query

    Thanks

    Saturday, August 19, 2017 5:31 PM

Answers

  • User347430248 posted

    Hi asp.ambur,

    Table:

    Query:

    WITH CTE AS
    (
        SELECT id, batch_no, category,date,
           RN = ROW_NUMBER() OVER (Partition BY batch_no
                                   ORDER BY date DESC)
        FROM batch_data
    )
    SELECT date, batch_no, category
    FROM CTE
    WHERE RN = 1

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 21, 2017 7:16 AM

All replies

  • User77042963 posted

    Select BatchNo ,Categor, [Date] from (

    Select *,row_number() Over(Partiiton by BatchNo Order by [Date] Desc) rn  

    from yourtable) t

    WHERE rn=1

    Monday, August 21, 2017 1:23 PM
  • User347430248 posted

    Hi asp.ambur,

    Table:

    Query:

    WITH CTE AS
    (
        SELECT id, batch_no, category,date,
           RN = ROW_NUMBER() OVER (Partition BY batch_no
                                   ORDER BY date DESC)
        FROM batch_data
    )
    SELECT date, batch_no, category
    FROM CTE
    WHERE RN = 1

    Output:

    Regards

    Deepak

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 21, 2017 7:16 AM