Post reply

Select only the specific number of newer dates from table

  • March 29, 2017 at 12:03 pm

    #336677

    Hi, from the sample code below, I am trying to get all the records from the 3 most recent dates, I thought by using " with ties" it would match the last result found based on the value in the "ORDER BY" . I  was expecting to bring all rows from  28, 27 and 24. Any help on how I can do that? Thank you!

    create table mytable (account_id int, event_time datetime, sign char(3));
    insert into mytable values
    (2251,'2017-03-28 19:00:04','YES')
    ,(2250,'2017-03-28 15:45:11','NO')
    ,(2250,'2017-03-28 01:01:45','NO')
    ,(2250,'2017-03-27 15:45:11','YES')
    ,(2250,'2017-03-27 01:01:45','NO')
    ,(2249,'2017-03-24 21:00:03','YES')
    ,(2248,'2017-03-24 05:45:10','NO')
    ,(2247,'2017-03-19 21:00:05','YES')
    ,(2246,'2017-03-19 05:45:10','NO')
    ,(2245,'2017-03-22 21:15:05','YES')

    select top 3 with ties
      account_id
    , event_time
    , sign
    from mytable
    order by convert(date,event_time) desc

  • March 29, 2017 at 12:12 pm

    #1935559

    a_car11 - Wednesday, March 29, 2017 12:03 PM

    Hi, from the sample code below, I am trying to get all the records from the 3 most recent dates, I thought by using " with ties" it would match the last result found based on the value in the "ORDER BY" . I  was expecting to bring all rows from  28, 27 and 24. Any help on how I can do that? Thank you!

    create table mytable (account_id int, event_time datetime, sign char(3));
    insert into mytable values
    (2251,'2017-03-28 19:00:04','YES')
    ,(2250,'2017-03-28 15:45:11','NO')
    ,(2250,'2017-03-28 01:01:45','NO')
    ,(2250,'2017-03-27 15:45:11','YES')
    ,(2250,'2017-03-27 01:01:45','NO')
    ,(2249,'2017-03-24 21:00:03','YES')
    ,(2248,'2017-03-24 05:45:10','NO')
    ,(2247,'2017-03-19 21:00:05','YES')
    ,(2246,'2017-03-19 05:45:10','NO')
    ,(2245,'2017-03-22 21:15:05','YES')

    select top 3 with ties
      account_id
    , event_time
    , sign
    from mytable
    order by convert(date,event_time) desc

    Try this and let me know if this works for you:
    create table mytable (account_id int, event_time datetime, sign char(3));
    insert into mytable values
    (2251,'2017-03-28 19:00:04','YES')
    ,(2250,'2017-03-28 15:45:11','NO')
    ,(2250,'2017-03-28 01:01:45','NO')
    ,(2250,'2017-03-27 15:45:11','YES')
    ,(2250,'2017-03-27 01:01:45','NO')
    ,(2249,'2017-03-24 21:00:03','YES')
    ,(2248,'2017-03-24 05:45:10','NO')
    ,(2247,'2017-03-19 21:00:05','YES')
    ,(2246,'2017-03-19 05:45:10','NO')
    ,(2245,'2017-03-22 21:15:05','YES');

    with top_dates as (

        select top 3 with ties convert(date,event_time) as event_date
        from mytable
        order by convert(date,event_time) desc
    )    
    select
        account_id,
        event_time,
        sign
    from mytable as mt
        INNER JOIN top_dates as td
            ON convert(date, mt.event_time) = td.event_date
    order by mt.event_time desc;

    drop table mytable;

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • March 29, 2017 at 12:21 pm

    #1935565

    This is what I get:

      account_idevent_timesign
    1225128.03.2017 19:00:04YES
    2225128.03.2017 19:00:04YES
    3225128.03.2017 19:00:04YES
    4225028.03.2017 15:45:11NO 
    5225028.03.2017 15:45:11NO 
    6225028.03.2017 15:45:11NO 
    7225028.03.2017 01:01:45NO 
    8225028.03.2017 01:01:45NO 
    9225028.03.2017 01:01:45NO

  • March 29, 2017 at 12:25 pm

    #1935568

    Use a CTE with DENSE_RANK().

    ;
    WITH mytable_dr AS
    (
        SELECT account_id, event_time, [sign],
            DENSE_RANK() OVER(ORDER BY CAST(event_time AS DATE) DESC) AS dr
        FROM #mytable
    )
    SELECT account_id, event_time, [sign]
    FROM mytable_dr
    WHERE dr <= 3

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • March 29, 2017 at 12:29 pm

    #1935570

    a_car11 - Wednesday, March 29, 2017 12:21 PM

    This is what I get:

      account_idevent_timesign
    1225128.03.2017 19:00:04YES
    2225128.03.2017 19:00:04YES
    3225128.03.2017 19:00:04YES
    4225028.03.2017 15:45:11NO 
    5225028.03.2017 15:45:11NO 
    6225028.03.2017 15:45:11NO 
    7225028.03.2017 01:01:45NO 
    8225028.03.2017 01:01:45NO 
    9225028.03.2017 01:01:45NO 
    That's what I get as well.   Is it correct?
    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)
     
      
    •  
  •   
     
     
     March 29, 2017 at 12:34 pm 
     
      #1935572  
          
         
     
    sgmunson - Wednesday, March 29, 2017 12:29 PM
    That's what I get as well.   Is it correct?
    No, this is what it should get:
      account_idevent_timesign
    1225128.03.2017 19:00:04YES
    2225028.03.2017 15:45:11NO 
    3225028.03.2017 01:01:45NO 
    4225027.03.2017 15:45:11YES
    5225027.03.2017 01:01:45NO 
    6224924.03.2017 21:00:03YES
    7224824.03.2017 05:45:10NO 
     
      
    •  
  •   
     
     
     March 29, 2017 at 12:43 pm 
     
      #1935575  
          
         
     
    drew.allen - Wednesday, March 29, 2017 12:25 PM
    Use a CTE with DENSE_RANK().
    ;
    WITH mytable_dr AS
    (
        SELECT account_id, event_time, [sign],
            DENSE_RANK() OVER(ORDER BY CAST(event_time AS DATE) DESC) AS dr
        FROM #mytable
    )
    SELECT account_id, event_time, [sign]
    FROM mytable_dr
    WHERE dr <= 3
    Drew
    Need to research on "DENSE_RANK()", but it works. Thank you!
     
      
    •  
  •   
     
     
     March 29, 2017 at 12:47 pm 
     
      #1935576  
          
         
     
    to fix Steve's, the CTE query needs a DISTINCT in it otherwise it returns 2017-03-28 3 times:

    with top_dates as (
        select DISTINCT top 3 with ties convert(date,event_time) as event_date
        from mytable
        order by convert(date,event_time) desc
    ) 
    select
        account_id,
        event_time,
        sign
    from mytable as mt
        INNER JOIN top_dates as td
            ON convert(date, mt.event_time) = td.event_date
    order by mt.event_time desc;

    that produces the proper results.  The DENSE_RANK one looks like it might be more efficient execution plan though
     
      
    •  
  •   
     
     
     March 29, 2017 at 12:48 pm 
     
      #1935579  
          
         
     
    TOP(n) WITH TIES is closer to RANK() rather than DENSE_RANK().  This is the reason that both queries that use TOP(n) WITH TIES don't give the desired results.  To see this, I've commented out one of the records in the sample to produce a situation where there are records that match the ties.
    create table #mytable (account_id int, event_time datetime, sign char(3));
    insert into #mytable values 
     (2251,'2017-03-28 19:00:04','YES')
    ,(2250,'2017-03-28 15:45:11','NO')
    --,(2250,'2017-03-28 01:01:45','NO')
    ,(2250,'2017-03-27 15:45:11','YES')
    ,(2250,'2017-03-27 01:01:45','NO')
    ,(2249,'2017-03-24 21:00:03','YES')
    ,(2248,'2017-03-24 05:45:10','NO')
    ,(2247,'2017-03-19 21:00:05','YES')
    ,(2246,'2017-03-19 05:45:10','NO')
    ,(2245,'2017-03-22 21:15:05','YES')
    SELECT *,
        RANK() OVER(ORDER BY CAST(event_time AS DATE) DESC),
        DENSE_RANK() OVER(ORDER BY CAST(event_time AS DATE) DESC)
    FROM #mytable
    WITH TIES will pull all records with the specified RANK(), but you want the records with the specified DENSE_RANK().
    Drew
    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA
     
      
    •  
  •   
     
     
     March 29, 2017 at 1:13 pm 
     
      #1935586  
          
         
     
    a_car11 - Wednesday, March 29, 2017 12:34 PM
    sgmunson - Wednesday, March 29, 2017 12:29 PM
    That's what I get as well.   Is it correct?
    No, this is what it should get:
      account_idevent_timesign
    1225128.03.2017 19:00:04YES
    2225028.03.2017 15:45:11NO 
    3225028.03.2017 01:01:45NO 
    4225027.03.2017 15:45:11YES
    5225027.03.2017 01:01:45NO 
    6224924.03.2017 21:00:03YES
    7224824.03.2017 05:45:10NO 
    Oops!   Here's a corrected version, which will not need the "with ties" option:
    create table mytable (account_id int, event_time datetime, sign char(3));
    insert into mytable values 
     (2251,'2017-03-28 19:00:04','YES')
    ,(2250,'2017-03-28 15:45:11','NO')
    ,(2250,'2017-03-28 01:01:45','NO')
    ,(2250,'2017-03-27 15:45:11','YES')
    ,(2250,'2017-03-27 01:01:45','NO')
    ,(2249,'2017-03-24 21:00:03','YES')
    ,(2248,'2017-03-24 05:45:10','NO')
    ,(2247,'2017-03-19 21:00:05','YES')
    ,(2246,'2017-03-19 05:45:10','NO')
    ,(2245,'2017-03-22 21:15:05','YES');
    with top_dates as (
        select top (3) event_date
        FROM (
            select distinct convert(date,event_time) as event_date
            from mytable
            ) AS X
        order by event_date desc
    )    
    select
        account_id,
        event_time,
        sign
    from mytable as mt
        INNER JOIN top_dates as td
            ON convert(date, mt.event_time) = td.event_date
    order by mt.event_time desc;
    drop table mytable;
    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)
     
      
    •  
  •   
     
     
     March 29, 2017 at 1:52 pm 
     
      #1935599  
          
         
     
    a_car11 - Wednesday, March 29, 2017 12:43 PM
    drew.allen - Wednesday, March 29, 2017 12:25 PM
    Use a CTE with DENSE_RANK().
    ;
    WITH mytable_dr AS
    (
        SELECT account_id, event_time, [sign],
            DENSE_RANK() OVER(ORDER BY CAST(event_time AS DATE) DESC) AS dr
        FROM #mytable
    )
    SELECT account_id, event_time, [sign]
    FROM mytable_dr
    WHERE dr <= 3
    Drew
    Need to research on "DENSE_RANK()", but it works. Thank you!
    An "order by account_id" in the end  is needed otherwise the latest time will get out of order
     
      
    •  
Viewing 11 posts - 1 through 10 (of 10 total)
 
You must be logged in to reply to this topic. Login to reply