How do I select top1 from table

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

Hi all,
I have a database with a table as shown below
columns
1.Tag
2.TimeStamp
3.Value

CSS

TAG1    2014-06-12 10:09:00.000 62.44384
TAG1    2014-06-12 10:09:01.000 37.97031
TAG1    2014-06-12 10:09:02.000 11.49679
TAG1    2014-06-12 10:09:03.000 84.02327
TAG1    2014-06-12 10:09:04.000 58.54975
TAG1    2014-06-12 10:09:05.000 32.07623
TAG1    2014-06-12 10:09:06.000 7.602705

--

CSS

TAG1    2014-06-12 10:09:54.000 80.34718
TAG1    2014-06-12 10:09:55.000 55.87366
TAG1    2014-06-12 10:09:56.000 29.40014
TAG1    2014-06-12 10:09:57.000 3.926617
TAG1    2014-06-12 10:09:58.000 76.45309
TAG1    2014-06-12 10:09:59.000 50.97957
TAG1    2014-06-12 10:10:03.000 2.876161
TAG1    2014-06-12 10:10:04.000 20.61197
TAG1    2014-06-12 10:10:05.000 93.13844
TAG1    2014-06-12 10:10:06.000 68.66492

--

CSS

TAG1    2014-06-12 10:10:56.000 90.46236
TAG1    2014-06-12 10:10:57.000 64.98883
TAG1    2014-06-12 10:10:58.000 38.51531
TAG1    2014-06-12 10:10:59.000 12.04179
TAG1    2014-06-12 10:11:00.000 86.56827
TAG1    2014-06-12 10:11:01.000 60.09475
TAG1    2014-06-12 10:11:02.000 34.62122
TAG1    2014-06-12 10:11:03.000 8.147702
TAG1    2014-06-12 10:11:04.000 81.67418

from the above table i want to select rows where seconds = 0,
that means i want the data 10:09:00, 10:10:00, 10:11:00 and so on.....,
if you observe the above data row with time stamp '10:10:00' is not there in this case i want a nearest row between 10:10:00 to 10:10:59 ( 10:10:03 in this case)

i want a sql query for this or vb.net code.
pls help me on this

-Thank you
-Srinivas

Posted 12-Jun-14 19:42pm

SrinivasEng

Updated 12-Jun-14 19:44pm

v2

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 13-Jun-14 2:48am

What have you tried?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andrius Leonavicius · Accepted Answer · 2014-06-13T00:51:00

Solution 2

Hi,

The following query should work in SQL Server 2005:

SQL

;WITH data
     AS (SELECT *,
                Row_Number()
                  OVER(
                    PARTITION BY DATEADD(mi, DATEDIFF(mi, 0, [TimeStamp]), 0)
                    ORDER BY [TimeStamp]) AS [RowNumber]
         FROM   [dbo].[YourTable])
SELECT [Tag],
       [TimeStamp],
       [Value]
FROM   data
WHERE  [RowNumber] = 1
ORDER  BY [TimeStamp];

Result:

XML

Tag    TimeStamp                 Value
TAG1   2014-06-12 10:09:00.000   62.44384
TAG1   2014-06-12 10:10:03.000   2.876161
TAG1   2014-06-12 10:11:00.000   86.56827

Posted 13-Jun-14 0:51am

Andrius Leonavicius

Comments

SrinivasEng 14-Jun-14 1:20am

Thanks alot..
It worked, but i don't know how it is working!.
If you don't mind can you give me some explanation (or) suggest me any links that have explanation on this...
and Thank you once again

Andrius Leonavicius 14-Jun-14 10:33am

You're welcome. Also, please accept the solution by clicking the "Accept Solution" button. Thank you.

I'll try to explain this in details:
1. DATEADD(mi, DATEDIFF(mi, 0, [TimeStamp]), 0)
This is used to remove seconds from DateTime (2014-06-14 17:22:49.287 => 2014-06-14 17:22:00.000)
2. ROW_NUMBER() OVER (PARTITION BY [...] ORDER BY [...])
2.1. PARTITION BY is grouping the results by [TimeStamp] without seconds (2014-06-12 10:09:00.000, 10:09:01.000, 10:09:02.000, ... belongs to one partition; 2014-06-12 10:10:03.000, 10:10:04.000, ... belongs to another partition and so on).
2.2. ROW_NUMBER() assigns the sequential number of a row within a partition (1, 2, 3, ...; 1, 2, ...; ...).
2.3. ORDER BY - gives row number by [TimeStamp] order (2014-06-12 10:09:00.000 - 1, 10:09:01.000 - 2, 10:09:02.000 - 3, ...).
3. ;WITH data AS [...]
This is a Common Table Expression (CTE) - a temporary named result set (a subquery). I'm using it here because this allows me to filter and order the results:
WHERE [RowNumber] = 1
ORDER BY [TimeStamp]

These links might be useful for you:
1. http://www.codeproject.com/Articles/308281/How-to-Use-ROW-NUMBER-to-Enumerate-and-Partition-R
2. http://www.codeproject.com/Articles/265371/Common-Table-Expressions-CTE-in-SQL-SERVER

SrinivasEng 16-Jun-14 1:52am

thankyou, I got it completely....

Andrius Leonavicius 16-Jun-14 8:51am

You're welcome. I'm glad to hear this.