Max Time of Max day

• irehman

  SSC-Addicted

  Points: 496

  More actions

  October 31, 2018 at 4:56 pm

  #376221

  Hello,

  I have a table called Transaction

  Date ——- Date Time ——— Status

  1/1/18 6:55 400

  1/12/18 6:20 500

  1/12/18 6:35 400

  1/12/18 6:40 400

  I’m trying to get

  1/1/18 6:55 400

  1/12/18 6:40 400

  Becauae both rows have status = 400 and they are the max time of each date.

  How do I write a sql to accomplish that?

  Thanks in advance.

• Jason A. Long

  SSC-Insane

  Points: 23786

  More actions

  October 31, 2018 at 5:30 pm

  #2011983

  irehman - Wednesday, October 31, 2018 4:56 PM

  Hello,I have a table called TransactionDate ——- Date Time ——— Status1/1/18 6:55 4001/12/18 6:20 5001/12/18 6:35 4001/12/18 6:40 400I’m trying to get 1/1/18 6:55 4001/12/18 6:40 400Becauae both rows have status = 400 and they are the max time of each date.How do I write a sql to accomplish that?Thanks in advance.

  Something like the following should do the trick...

  WITH
    cte_AddRN AS (
        SELECT
            t.[Date],
            t.[Time],
            t.[Status],
            rn = ROW_NUMBER() OVER (PARTITION BY t.[Date] ORDER BY t.[Time] DESC)
        FROM
            dbo.Transactions t
        )
SELECT
    *
FROM
    cte_AddRN ar
WHERE
    ar.rn = 1;

• Jeff Moden

  SSC Guru

  Points: 1004434

  More actions

  October 31, 2018 at 6:49 pm

  #2011988

  Jason A. Long - Wednesday, October 31, 2018 5:30 PM

  irehman - Wednesday, October 31, 2018 4:56 PM

  Hello,I have a table called TransactionDate ——- Date Time ——— Status1/1/18 6:55 4001/12/18 6:20 5001/12/18 6:35 4001/12/18 6:40 400I’m trying to get 1/1/18 6:55 4001/12/18 6:40 400Becauae both rows have status = 400 and they are the max time of each date.How do I write a sql to accomplish that?Thanks in advance.

  Something like the following should do the trick...

  WITH
    cte_AddRN AS (
        SELECT
            t.[Date],
            t.[Time],
            t.[Status],
            rn = ROW_NUMBER() OVER (PARTITION BY t.[Date] ORDER BY t.[Time] DESC)
        FROM
            dbo.Transactions t
        )
SELECT
    *
FROM
    cte_AddRN ar
WHERE
    ar.rn = 1;

  This would have been a whole lot easier if they hadn't split the date and time into two columns.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  November 1, 2018 at 5:13 am

  #2012015

  Jason A. Long - Wednesday, October 31, 2018 5:30 PM

  irehman - Wednesday, October 31, 2018 4:56 PM

  Hello,I have a table called TransactionDate ——- Date Time ——— Status1/1/18 6:55 4001/12/18 6:20 5001/12/18 6:35 4001/12/18 6:40 400I’m trying to get 1/1/18 6:55 4001/12/18 6:40 400Becauae both rows have status = 400 and they are the max time of each date.How do I write a sql to accomplish that?Thanks in advance.

  Something like the following should do the trick...

  WITH
    cte_AddRN AS (
        SELECT
            t.[Date],
            t.[Time],
            t.[Status],
            rn = ROW_NUMBER() OVER (PARTITION BY t.[Date] ORDER BY t.[Time] DESC)
        FROM
            dbo.Transactions t
        )
SELECT
    *
FROM
    cte_AddRN ar
WHERE
    ar.rn = 1;

  Here is a boring version - no CTE's, no row numbering:

  SELECT [Date], MAX([Time], Status
from dbo.Transactions
group by [Date], Status

  _____________
  Code for TallyGenerator