ansaurus

Question

Help to the way to write a query for the requirement

Answer 1

+3 A:

WITH RankedRealTimeData AS (
  SELECT *, ROW_NUMBER() OVER (
      PARTITION BY CONVERT(VARCHAR(10), [TIME], 121) 
      ORDER BY Time DESC) AS RN
  FROM RealTimeData
)
SELECT * FROM RankedRealTimeData WHERE RN=1;

Bill Karwin 2010-04-28 02:07:00

Shouldn't that be `DATEPART(DAYOFYEAR, Time)`?

ongle 2010-04-28 02:11:21

Don't you need another argument for the DATEPART function?

RBarryYoung 2010-04-28 02:13:17

The query returns the correct result if you make the change to the `DATEPART` function as indicated above.

ongle 2010-04-28 02:45:18

My bad. I have edited the function above.

Bill Karwin 2010-04-28 02:57:09

This will not differentiate between records from different years. You could partition by CONVERT(VARCHAR(10), [TIME], 121) instead of DATEPART

Chris Bednarski 2010-04-28 03:24:20

@Knoodles: thanks, done. This is a team effort!

Bill Karwin 2010-04-28 04:07:19

@Chris good point. I wonder what gives better performance, casting it as a `VARCHAR` or having multiple `PARTITION BY` criteria (`DATEPART` year, `DATEPART` dayofyear)

ongle 2010-04-28 15:08:34

@ongle: Both methods defeat the possibility of using an index, which I would guess (without having measured, admittedly) is a much greater performance penalty than the difference between two ways of extracting the components of the date. So if performance is a priority, I would do as you suggest in your comment to the OP question above, store components of the date separately, and index them.

Bill Karwin 2010-04-28 18:03:34

@Bill Karwin, rather than splitting up the real date/time field into two fields which would change how the rest of the application interacts with this table the OP can create a persisted calculated field on the table which is just the date portion and then he can create an index on that persisted calculated field.

Sam 2010-04-29 15:36:17

@Sam: Good point, I overlook the feature of calculated fields (and expression indexes) since I most often use MySQL.

Bill Karwin 2010-04-30 18:48:35

Answer 2

+2 A:

SELECT 
    CAST(Time as DATE) EodDate, 
    (
        SELECT  TOP 1
                Value
        FROM    RealtimeData I
        WHERE   CAST(I.Time AS Date) = CAST(O.Time AS Date)
        ORDER BY    Time DESC
    ) EodValue
FROM 
    RealtimeData O
GROUP BY CAST(Time as DATE)
ORDER BY CAST(Time as DATE)

Sam 2010-04-28 02:13:12

The `DATE` datatype is new with SQL Server 2008.

ongle 2010-04-28 02:17:59

@ongle, thanks for pointing that out. I didn't realize. Anyways, there are other simple ways to get the date component if you need something that's not sql2008 specific.

Sam 2010-04-28 02:25:03

Answer 3

A:

Using Sql 92 is the best solution for anyone who dont want to use specified DB system.

Like this:

Select A.*
From RealtimeData A
Where A.RTime >= (
    select Max(B.RTime)
    From RealtimeData B
    Where Cast((B.RTime - A.RTime) as int) <= 0
)

silent.hill 2010-04-28 06:59:39

Very banana. No optimization.

Lu Lu 2010-04-28 07:05:11

Answer 4

A:

;With wcte as ( Select vTime,vValue,Row_Number() over (partition by Convert(DateTime,Convert(varchar(10),vTime,110)) order by vTime Desc) rno from @vTable )Select vTime,vValue from wcte where rno = 1

Atif Sheikh 2010-04-30 04:23:12

ansaurus

tags:

views:

answers:

Help to the way to write a query for the requirement

related questions