locked
Seemingly simple SQL query RRS feed

  • Question

  • I've got a seemingly simple task that I hope someone will help me with. The situation is that I've got a table with a time stamp and one or more columns with assigned data values. The question/query I need to pose is what is the value with the latest time stamp for every day and its associated value. In other words what is the value of the time stamp and value closest to 12 midnight without going over into the next day.

    My attempts so far have only resulted in errors thrown by using aggregate types and I've not yet stumbled upon the correct syntax?

    Thanks everyone for reading this post.

    MW

    Tuesday, January 20, 2015 6:11 AM

Answers

  • Hey,

    You can refer below query.In my case i have created Table with three columns as Timestamp_col <datetime>,Column_1 <bigint>,Column_2 <varchar(50)>

    Below is the required query

     Select * from Table_1 
     where CAST(Timestamp_col as time) IN (Select MAX(CAST(Timestamp_col as time))
     from Table_1 
     Group BY CAST(Timestamp_col as Date))

    • Marked as answer by mlwest Monday, January 26, 2015 10:35 PM
    Tuesday, January 20, 2015 8:52 AM

All replies

  • Could you provide the query which you tried?


    -Vaibhav Chaudhari

    Tuesday, January 20, 2015 6:16 AM
  • Hey,

    You can refer below query.In my case i have created Table with three columns as Timestamp_col <datetime>,Column_1 <bigint>,Column_2 <varchar(50)>

    Below is the required query

     Select * from Table_1 
     where CAST(Timestamp_col as time) IN (Select MAX(CAST(Timestamp_col as time))
     from Table_1 
     Group BY CAST(Timestamp_col as Date))

    • Marked as answer by mlwest Monday, January 26, 2015 10:35 PM
    Tuesday, January 20, 2015 8:52 AM
  • SELECT
    [Timestamp]
    FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY DATEDIFF(dd,0,[Timestamp]) ORDER BY [Timestamp] DESC) AS Seq,*
    FROM Table
    )t
    WHERE Seq = 1 

    Another way is this

    SELECT *
    FROM Table t
    WHERE NOT EXISTS 
    (
    SELECT 1
    FROM Table
    WHERE DATEDIFF(dd,0,[Timestamp]) >= DATEDIFF(dd,0,t.[Timestamp])
    AND [Timestamp] > t.[Timestamp]
    )


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Tuesday, January 20, 2015 9:21 AM
    • Proposed as answer by Naomi N Tuesday, January 20, 2015 10:29 PM
    Tuesday, January 20, 2015 9:17 AM
  • Hi 

    Always provide a DDL along with some data, so that it will be easy for others to understand and help with the questions

    Also try with simple Order by/Top as shown below

    select top 1 *
    from Table
    order timestamp_col desc


    Regards, Dineshkumar,
    Please Mark as Answer if my post answers your question and Vote as Helpful if it helps you

    Dineshkumar&#39;s BI Blog

    Tuesday, January 20, 2015 2:17 PM
  • SELECT *
      FROM myTable t
     WHERE myTimestamp = (SELECT MAX(myTimestamp) FROM myTable WHERE myIDCol = t.myIDCol)

    That ought to do it.
    Tuesday, January 20, 2015 2:35 PM
  • >> In other words what is the value of the time stamp and value closest to 12 midnight without going over into the next day.

    So to clear my confusion first, is the data type of the column is timestamp? or a datetime? The timestamp datatype has nothing to do with the date and time, its just unique binary value for a given database.

    Please refer this article for details 

    http://technet.microsoft.com/en-us/library/aa260631(v=sql.80).aspx

    If the data type is datetime, your query would be a very easier one. Could you show us some sample data and expected result?


    Satheesh
    My Blog | How to ask questions in technical forum

    Tuesday, January 20, 2015 2:37 PM
  • So it should be apparent by now that a script that demonstrates your issue helps everyone understand your issue.  Perhaps the following provides the gist of one possible approach.  Pay attention to the where clause in the final query.  Comment it out to see how the logic works.

    set nocount on;
    declare @data table (
      data_id int not null identity(1, 1)
    , tstamp datetime not null 
    , xx  int not null);
    
    insert @data (tstamp, xx) values ('20141231 11:15', 10), ('20141231', 12), (CURRENT_TIMESTAMP, 11), (dateadd(hour, -1, CURRENT_TIMESTAMP), 6), ('20150115 23:59:59', 25);
    select * from @data order by tstamp;
    
    with cte as (select data_id, tstamp, xx, row_number() over (partition by cast(tstamp as date) order by tstamp desc) as rno
    	from @data
    )
    select * from cte 
    where rno = 1
    order by cast(tstamp as date), rno;
    
    

    • Proposed as answer by Naomi N Tuesday, January 20, 2015 10:29 PM
    Tuesday, January 20, 2015 9:11 PM
  • First of all - thanks everyone for responding so soon. I've been on a job site swapping out a complicated equipment rack and didn't have time to try the various suggestions you all have provided.

    Unfortunately I'm stuck supervising some fiber installation work tomorrow and won't have time to give it a try until Thursday but then I'll jump on it and let you all know.

    Thanks again everyone.

    Wednesday, January 21, 2015 3:42 AM
  • Thanks for your reply Satheesh. The data type of my timestamp column is datetime2.

    The structure of the table is pretty straight forward with a timestamp column and a number of, what is called in this environment, tag names that are mainly floats with some tiny integers in the mix.

    I can't believe this should be hard to do as it sure looks to me to be a very common query - I'm just overthinking it.

    Sunday, January 25, 2015 6:32 PM
  • >>The data type of my timestamp column is datetime2.

    Okay, So here is an example solution, you might need to make adjustments according to your database designs, columns etc. This is just a simple demonstration of the logic

    USE DemoDB;
    IF NOT OBJECT_ID('[dbo].[mytable]') IS  NULL 
    	DROP TABLE [dbo].[mytable]
    Go
    CREATE TABLE [dbo].[mytable]
        (
          [ID] [INT] NOT NULL PRIMARY KEY,
          [Name] [VARCHAR](255) NULL ,
          [updatetime] [DATETIME2](7) NULL
        );
    
    INSERT  [dbo].[mytable]
            ( [ID], [Name], [updatetime] )
    VALUES  ( 1, N'Rooney', CAST(0x07809950AB8001380B AS DATETIME2) ),
            ( 2, N'Rooney', CAST(0x07000366F7AD01380B AS DATETIME2) ),
            ( 3, N'Neve', CAST(0x0700C58F17A201380B AS DATETIME2) ),
            ( 4, N'Rama', CAST(0x0780E5788C3501380B AS DATETIME2) ),
            ( 5, N'Charlotte', CAST(0x0780E4E5D12E01380B AS DATETIME2) ),
            ( 6, N'Neve', CAST(0x0780E0BDE45101380B AS DATETIME2) ),
            ( 7, N'Rama', CAST(0x07803468C40501380B AS DATETIME2) ),
            ( 8, N'Charlotte', CAST(0x07803EC0BB9701380B AS DATETIME2) );

    Here we have setup some sample data. and here is how your solution would look like 

    WITH CTE AS(
    SELECT *, ROW_NUMBER()OVER(PARTITION BY name ORDER BY updatetime DESC) AS RowNumber FROM dbo.mytable 
    WHERE updatetime BETWEEN '20140101' AND '20140101 23:59:59')
    SELECT id,name,updatetime  FROM CTE WHERE CTE.RowNumber=1

    This is how you could do this incase you need data for multiple dates, You need to include the date too in the partition clause of the row_number.

    INSERT	INTO dbo.mytable
            SELECT ID+(SELECT max (id) FROM dbo.mytable),Name,DATEADD(DAY,1,updatetime) FROM dbo.mytable;
    
    WITH CTE AS(
    SELECT *, ROW_NUMBER()OVER(PARTITION BY name, CAST(updatetime AS DATE)  ORDER BY updatetime DESC) AS RowNumber FROM dbo.mytable)
    SELECT id,name,updatetime FROM CTE WHERE CTE.RowNumber=1;

    Hope this helps


    Satheesh
    My Blog | How to ask questions in technical forum


    Monday, January 26, 2015 8:03 AM
  • if you use datetime datatype for date and time

    Select	Top 1 *
    From	<table_name> 
    Where	Convert(Date,<Datetime_column>) = Convert(Date,GETDATE())
    Order	By <Datetime_column> Desc

    if you use seperate column for date and time

    Select Top 1 *
    From <table_name>  
    Where Convert(Date,<date_column>) = Convert(Date,GETDATE())
    Order By <time_stamp_column> Desc
    

    Monday, January 26, 2015 8:21 AM
  • Thanks for your many replies everyone - I learned from each of them.

    What I ended up using was the reply from Tech Aspirant which started out as below:

    Select * from Table_1
    where CAST(Timestamp_col as time) IN (Select MAX(CAST(Timestamp_col as time))
    from Table_1
    Group BY CAST(Timestamp_col as Date))

    And I was able to modify it as follows:

    SELECT My_Timestamp_Col, My_Tag_Name
    FROM My_Table_Name WHERE CAST(My_Timestamp_Col as Time) IN (SELECT MAX(CAST(My_Timestamp_Col
     as Time)) FROM My_Table_Name GROUP BY CAST(My_Timestamp as Date))



    • Edited by mlwest Monday, January 26, 2015 10:48 PM
    Monday, January 26, 2015 10:44 PM