locked
how to get this result in sql RRS feed

  • Question

  • User269846090 posted

    ID   Name     Date                          Area

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

    1      sheet1        2016-09-01 11:05     a

    2      sheet2        2016-09-03  03:50    b 

    3      sheet3        2016-09-03  03.56    c

    4      sheet4        2016-09-03  03.59    d

    5      sheet5        2016-09-04   04:52   e

    I want the below result. 

    ID   Name     Date                     Area

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

    1      sheet1        2016-09-01  11:05     a

    4      sheet4        2016-09-03  03.59    d

    5      sheet5        2016-09-04   04:52   e

    Thursday, October 6, 2016 11:11 AM

Answers

  • User77042963 posted
    create table test(ID int,  Name varchar(20),   [Date]  Datetime , Area char(1))
    Insert into test values (1,'sheet1','2016-09-01 11:05','a')
    ,(2,'sheet2','2016-09-03 03:50','b')
    ,(3,'sheet3','2016-09-03 03:56','c')
    ,(4,'sheet4','2016-09-03 03:59','d')
    ,(5,'sheet5','2016-09-04 04:52','e')
     
     
     Select ID,  Name, Date,Area from (
    select ID,  Name, Date,Area
    , row_number() Over(Partition by Cast([Date] as date) Order by [Date] Desc) rn from test
    ) t
    Where rn=1
    
    
    drop table test
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 6, 2016 1:45 PM

All replies

  • User2009744128 posted

    Hi ssjGanesh,

    You can use the datediff method to get the difference between the dates in your table and the current timestamp :

    Something like this:

    SELECT * from tbl_Example
     where datediff(minute,getdate(),date) between 0 and 30000 

    so that will give you results 30000 minutes from now, you can update the query accordingly.

    Hope this helps.

    Thursday, October 6, 2016 12:31 PM
  • User77042963 posted
    create table test(ID int,  Name varchar(20),   [Date]  Datetime , Area char(1))
    Insert into test values (1,'sheet1','2016-09-01 11:05','a')
    ,(2,'sheet2','2016-09-03 03:50','b')
    ,(3,'sheet3','2016-09-03 03:56','c')
    ,(4,'sheet4','2016-09-03 03:59','d')
    ,(5,'sheet5','2016-09-04 04:52','e')
     
     
     Select ID,  Name, Date,Area from (
    select ID,  Name, Date,Area
    , row_number() Over(Partition by Cast([Date] as date) Order by [Date] Desc) rn from test
    ) t
    Where rn=1
    
    
    drop table test
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 6, 2016 1:45 PM
  • User-1716253493 posted
    SELECT * FROM tbl WHERE id IN (SELECT MAX(ID) FROM tbl GROUP BY CAST([date] as date))

    Thursday, October 6, 2016 4:55 PM