June 1, 2016 at 7:04 am
Hi Hope someone can help me out
I have a table like the one Below
TagIndex DateAndTime Value
0 30-05-2016 01:00:00 11,5
0 30-05-2016 02:00:00 10,22
1 30-05-2016 03:00:00 16
0 30-05-2016 04:00:00 35
2 30-05-2016 05:00:00 100
0 30-05-2016 09:00:00 22
1 30-05-2016 13:00:00 66
I would like to make a request into the table with a where Tagindex = 0, search for the Higest Value and get back the value, DateandTime, and Tagindex
So asking for Tagindex 0 should give me the following result
TagIndex DateAndTime Value
0 30-05-2016 04:00:00 35
So asking for Tagindex 1 should give me the following result
1 30-05-2016 13:00:00 66
June 1, 2016 at 7:17 am
Use
WHERE to filter the data
ORDER BY to put the data in the required order
TOP to restrict the output to one row
Post what you have so far and someone will help
Far away is close at hand in the images of elsewhere.
Anon.
June 1, 2016 at 7:17 am
tinojoergensen (6/1/2016)
Hi Hope someone can help me outI have a table like the one Below
TagIndex DateAndTime Value
0 30-05-2016 01:00:00 11,5
0 30-05-2016 02:00:00 10,22
1 30-05-2016 03:00:00 16
0 30-05-2016 04:00:00 35
2 30-05-2016 05:00:00 100
0 30-05-2016 09:00:00 22
1 30-05-2016 13:00:00 66
I would like to make a request into the table with a where Tagindex = 0, search for the Higest Value and get back the value, DateandTime, and Tagindex
So asking for Tagindex 0 should give me the following result
TagIndex DateAndTime Value
0 30-05-2016 04:00:00 35
So asking for Tagindex 1 should give me the following result
1 30-05-2016 13:00:00 66
You can do this easily using TOP.
Select top 1
TagIndex
, DateAndTime
, Value
from YourTable
where TagIndex = 1
order by DateAndTime desc
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2016 at 7:23 am
Sean Lange (6/1/2016)
You can do this easily using TOP.
Too easy when you give the answer 😛
Mind you it's what I normally do 😀
Far away is close at hand in the images of elsewhere.
Anon.
June 1, 2016 at 7:36 am
It may look easy, but still the solution provided was wrong.
Here's one that does give the requested answers:
declare @tbl table (
TagIndex int not null,
DateAndTime datetime not null,
Value decimal(10,2) not null
)
insert @tbl(TagIndex, DateAndTime, Value)
values
(0, {ts '2016-05-30 01:00:00'}, 11.5),
(0, {ts '2016-05-30 02:00:00'}, 10.22),
(1, {ts '2016-05-30 03:00:00'}, 16),
(0, {ts '2016-05-30 04:00:00'}, 35),
(2, {ts '2016-05-30 05:00:00'}, 100),
(0, {ts '2016-05-30 09:00:00'}, 22),
(1, {ts '2016-05-30 13:00:00'}, 66)
select top(1) t.Value, t.DateAndTime, t.TagIndex
from @tbl t
where t.TagIndex = 0
order by t.Value desc;
select top(1) t.Value, t.DateAndTime, t.TagIndex
from @tbl t
where t.TagIndex = 1
order by t.Value desc;
To list highest values for each TagIndex I like to use a common table expression:
with cte as (
select
row_number() over (partition by t.TagIndex order by t.Value desc) as seq,
t.Value,
t.DateAndTime,
t.TagIndex
from @tbl t
)
select c.Value, c.DateAndTime, c.TagIndex
from cte c
where c.seq = 1
order by c.TagIndex
June 1, 2016 at 7:46 am
R.P.Rozema (6/1/2016)
It may look easy, but still the solution provided was wrong.Here's one that does give the requested answers:
While your code works well for the entire set the OP specifically stated they wanted only 1 row returned.
I would like to make a request into the table with a where Tagindex = 0, search for the Higest Value and get back the value, DateandTime, and Tagindex
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2016 at 7:47 am
R.P.Rozema (6/1/2016)
It may look easy, but still the solution provided was wrong.
If you replace DateAndTime with Value in the ORDER BY, it will be correct 🙂
Far away is close at hand in the images of elsewhere.
Anon.
June 2, 2016 at 11:11 am
Sean Lange (6/1/2016)
R.P.Rozema (6/1/2016)
It may look easy, but still the solution provided was wrong.Here's one that does give the requested answers:
While your code works well for the entire set the OP specifically stated they wanted only 1 row returned.
I would like to make a request into the table with a where Tagindex = 0, search for the Higest Value and get back the value, DateandTime, and Tagindex
:hehe: You're right Sean. And that's why my code in the first code block does exactly that, right? :satisfied: The code in the 2nd block just elaborates on this to show how to use a cte to list the results for all Tagindex values. And yes, I understand you knew very well the Value column should have been in the order by, but as presented your solution could confuse the OP, as it doesn't return the requested results.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply