Seemingly simple SQL query

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

Answer 1

Could you provide the query which you tried?

---

-Vaibhav Chaudhari

Answer 2

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))

Answer 3

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

Answer 4

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

Answer 5

SELECT *
  FROM myTable t
 WHERE myTimestamp = (SELECT MAX(myTimestamp) FROM myTable WHERE myIDCol = t.myIDCol)

That ought to do it.

Answer 6

>> 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

Answer 7

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;

Answer 8

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.

Answer 9

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.

Answer 10

>>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

Answer 11

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

Answer 12

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))