September 17, 2015 at 7:05 pm
Hi all,
I have a column in a table which holds list of values. I would like to find the first value, the highest value(Peak Value), and the last value in the list of values. How do you write a T-SQL query to get the desired result? Also can it be achieved using a Cursor?
Example values:
1
2
2.1
1.5
0.8
0.9
2.5
0.1
Could you please provide code examples?
Thank you.
September 17, 2015 at 7:12 pm
Sounds like homework. Show us what you tried.
September 17, 2015 at 7:26 pm
ITU_dk2012 (9/17/2015)
the last value in the list of values
You can't - no concept of "order" in the data - unless there is some other column, in the table, that you can sequence by to define "last"? - perhaps a Date/Time, or an ID column (although that can be risky if, at some time in the future, the ID might hit the Maximum and be reset to a, lower, start value - but I grant that that is probably a pedantic point 🙂 )
September 17, 2015 at 8:00 pm
It's not an id column and there are other columns as well in the table. I just need some code examples to find the desired result. I have searched so much on google but can't find a relevant example. Any ideas and code examples?
Thank you.
September 17, 2015 at 8:02 pm
ITU_dk2012 (9/17/2015)
Hi all,I have a column in a table which holds list of values. I would like to find the first value, the highest value(Peak Value), and the last value in the list of values. How do you write a T-SQL query to get the desired result? Also can it be achieved using a Cursor?
Example values:
1
2
2.1
1.5
0.8
0.9
2.5
0.1
Could you please provide code examples?
Thank you.
As Kristen said, you have no way to distinguish between oldest, newest and everything in between.. Below is some sample data with a random date you can use to get the newest and oldest record.
-- Create sample data with a date column to determine what's newest and oldest
DECLARE @T table (DateEntered date, SomeValue decimal(3,1))
INSERT @T
SELECT CAST(GETDATE()-ABS(CHECKSUM(newid())%100)+10 AS date), v
FROM
(VALUES (1),(2),(2.1),(1.5),(0.8),(0.9),(2.5),(0.1)) t(v);
-- Check the table contents
SELECT * FROM @T ORDER BY DateEntered;
-- Get newest, oldest and peek
WITH X AS
(
SELECT
peek = CAST(MAX(SomeValue) AS varchar(20)),
oldest = CAST(MAX(DateEntered) AS varchar(20)),
newest = CAST(MIN(DateEntered) AS varchar(20))
FROM @T
)
SELECT
xx,
MAX
(
CASE xx
WHEN 'oldest' THEN oldest
WHEN 'newest' THEN newest
WHEN 'peek' THEN peek
END
)
FROM X
CROSS APPLY (VALUES('peek'),('oldest'),('newest')) xx(xx)
GROUP BY xx;
-- Itzik Ben-Gan 2001
September 20, 2015 at 11:47 am
Thank you for the code example. I am just learning T-SQL and don't know the advanced stuff in T-SQL. I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. Could you please look at this code and provide me better solution by using Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
DECLARE
@FirstName
@LAstname
@Age
@FirstValue,
@HighestValue
@LastValue
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
FOR
SELECT x.value AS FirstValue
FROM TABLE x
INNER JOIN TABLE y
ON x.id = y.id
WHERE someid = '123xyx'
OPEN TESTCURSOR
-- get the first value
FETCH FIRST FROM TESTCURSOR INTO @FirstValue
-- second Cursor
DECLARE TESTCURSOR2 CURSOR
DYNAMIC
FOR
SELECT MAX(x.value) AS HighestValue
FROM TABLE x
INNER JOIN TABLE y
ON x.id = y.id
WHERE someid = '123xyx' -- used same id as above in the first cursor in where clause
OPEN TESTCURSOR2
-- get the next highest value by using MAX function above
FETCH NEXT FROM TESTCURSOR2 INTO @HighestValue -- Getting the MAX highest value
CLOSE TESTCURSOR2
DEALLOCATE TESTCURSOR2
-- get the last value from TESTCURSOR (The first Cursor
FETCH LAST FROM TESTCURSOR INTO @LastValue
-- close the first cursor
CLOSE TESTCURSOR
DEALLOCATE TESTCURSOR
-- select all the values
SELECT @FirstValue AS InitialValue, @HighestValue AS Hisghest, , @LastValue AS Last
The above code is totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS
and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
DECLARE
@FirstName
@LAstname
@Age
@FirstValue,
@HighestValue
@LastValue
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
Could you please make the corrections in the above code examples and use CURSORS TO get the desired results?
Thank you.
September 21, 2015 at 9:48 am
ITU_dk2012 (9/20/2015)
@Alan,Thank you for the code example. I am just learning T-SQL and don't know the advanced stuff in T-SQL. I am using Cursors to get the first value, the last value and the peak value and some other values from other tables. I found some examples on google but the code I am using is mixed up. I am using multiple Cursors. Could you please look at this code and provide me better solution by using Cursors. I need to join three tables to get the result set into the Cursor. The first example uses 2 tables.
DECLARE
@FirstName
@LAstname
@Age
@FirstValue,
@HighestValue
@LastValue
-- First Cursor
DECLARE TESTCURSOR CURSOR
DYNAMIC
FOR
SELECT x.value AS FirstValue
FROM TABLE x
INNER JOIN TABLE y
ON x.id = y.id
WHERE someid = '123xyx'
OPEN TESTCURSOR
-- get the first value
FETCH FIRST FROM TESTCURSOR INTO @FirstValue
-- second Cursor
DECLARE TESTCURSOR2 CURSOR
DYNAMIC
FOR
SELECT MAX(x.value) AS HighestValue
FROM TABLE x
INNER JOIN TABLE y
ON x.id = y.id
WHERE someid = '123xyx' -- used same id as above in the first cursor in where clause
OPEN TESTCURSOR2
-- get the next highest value by using MAX function above
FETCH NEXT FROM TESTCURSOR2 INTO @HighestValue -- Getting the MAX highest value
CLOSE TESTCURSOR2
DEALLOCATE TESTCURSOR2
-- get the last value from TESTCURSOR (The first Cursor
FETCH LAST FROM TESTCURSOR INTO @LastValue
-- close the first cursor
CLOSE TESTCURSOR
DEALLOCATE TESTCURSOR
-- select all the values
SELECT @FirstValue AS InitialValue, @HighestValue AS Hisghest, , @LastValue AS Last
The above code is totally inefficient but it gives the correct result. Now I want to pull some more value and join a third table (TABLE z) in the above CURSORS
and not sure how to make it working using CURSORS.I would like to use the following in the CURSORS above.
DECLARE
@FirstName
@LAstname
@Age
@FirstValue,
@HighestValue
@LastValue
SELECT x.publishdate, y.firstname, y.lastname, y.age, z.initialValue AS FirstValue, z.HighestValue AS Highest, z.LastValue AS Last
FROM TABLE x
LEFT OUTER JOIN TABLE y
ON x.id = y.id
INNER JOIN TABLE z
ON x.id = z.id
Could you please make the corrections in the above code examples and use CURSORS TO get the desired results?
Thank you.
I am not very good with cursors to be honest with you. All I can suggest is that you copy/paste the code I provided and play around with that. I think that you'll find that doing it the way I posted is actually not that advanced. A cursor is a more complex solution IMHO.
-- Itzik Ben-Gan 2001
September 21, 2015 at 10:07 am
Is there a reason you are trying to use a cursor here? Cursors are horribly inefficient and not be used for this type of thing in the real world. If this is for a class and your teacher mandates cursor usage that is another thing. Even though a cursor is really not the right tool for this you can do it. I can help but you need to provide some more details here. Can you post the sample data along with some data and the desired output? Please take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
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/
September 21, 2015 at 10:11 am
PLEASE don't cross post. You have at least two threads for the exact same issue going on. This only fragments replies and makes it more difficult for you AND the volunteers around here trying to help. For anybody interested here is the other copy. http://www.sqlservercentral.com/Forums/Topic1721196-391-1.aspx
_______________________________________________________________
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/
September 21, 2015 at 10:12 am
Why did you open this other thread for the same problem?
http://www.sqlservercentral.com/Forums/Topic1721196-391-1.aspx
It only divides the answers and multiplies the efforts of people helping in here.
September 21, 2015 at 10:57 am
Luis Cazares (9/21/2015)
Why did you open this other thread for the same problem?http://www.sqlservercentral.com/Forums/Topic1721196-391-1.aspx
It only divides the answers and multiplies the efforts of people helping in here.
...and makes it less likely that other people will want to assist you.
But please post your DDL (CREATE TABLE) and INSERT scripts (scrub your sample data if you have to) and we'll try to assist you better. Off the top of my head, I see a couple of ways this could go. But again, I'd like to know the reason for needing the cursor as there are quite a few methods better suited to what you're trying to accomplish.
September 21, 2015 at 6:26 pm
Sorry my mistake. I will post my questions in one place next time.
Thank you.
October 18, 2015 at 4:29 pm
@ Alan Burstein,
Sorry for my late reply. I tried your example but no luck. I am looking for the first value, the maximum value and the last value in the list of values. But your example gives incorrect result. Please note that I am not looking for date related values which were entered in the table on a certain date. I am just looking to grab the first value, the maximum value and the last value in the list of values.
I have the following table with some sample data.
CustomerIDTestValues
1 1.00
22.10
31.50
40.80
50.90
62.50
70.10
October 18, 2015 at 4:41 pm
@ Alan Burstein,
Sorry for my late reply. I tried your example but no luck.
I am looking for the first value, the maximum value and the last value in the list of values. But your example gives incorrect result.
Please note that I am not looking for date related values which were entered in the table on a certain date. I am just looking to grab the first value, the maximum value and the last value
in the list of values.
I have the following table with some sample data.
CustomerIDTestValues
1 1.00
2 2.10
3 1.50
4 0.80
5 0.90
6 2.50
7 0.10
When I use your code example I get the following result: The result follows after this SQL query
WITH X AS
(
SELECT
peek = CAST(MAX(TestValues) AS varchar(20)),
oldest = CAST(MAX(TestValues) AS varchar(20)),
newest = CAST(MIN(TestValues) AS varchar(20))
FROM test_2
)
SELECT
xx,
MAX
(
CASE xx
WHEN 'oldest' THEN oldest
WHEN 'newest' THEN newest
WHEN 'peek' THEN peek
END
)
FROM X
CROSS APPLY (VALUES('peek'),('oldest'),('newest')) xx(xx)
GROUP BY xx;
The result of the above query:
xx (No column name)
peek 2.50
oldest2.50
newest0.10
But the result should give me 1.00 as the first value, 2.50 as second value which is correct and the last value looks correct but there may be many other minimum values in the list.
SO how can we achieve this to get the first value, the maximum value and the last value in a list of values? The maximum part seems to be working fine.
Thank you.
October 18, 2015 at 4:42 pm
Sorry posted twice.
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply