September 20, 2015 at 5:56 pm
Hi all,
I have basic knowledge of T-SQL and 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.
@FirstName NVARCHAR,
@LastName NVARCHAR,
@FirstValue decimal,
@HighestValue decimal,
@LastValue decimal
-- 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 seems 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.
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 provide some help and make the above code working?
Thank you.
September 20, 2015 at 7:16 pm
Why do you want to use cursors for this? "Cursor" is a dirty word around here. You could probably do all this with either aggregate or window functions.
September 20, 2015 at 7:52 pm
Could you provide some examples with Aggregates or Windows functions using the code I provided?
Thank you.
September 20, 2015 at 8:00 pm
Got some table definitions and some fake data? (Fake if it's confidential... representative is fine). And expected output?
September 20, 2015 at 8:06 pm
You can have a look at the code example I provided in the first post.
Thanks.
September 20, 2015 at 9:36 pm
Maybe start here:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
and maybe this:
http://spaghettidba.com/?s=how+to+post
Look at your question from our perspective. How are we to test any of it? Without data, we can't. If you set up the question so that folks can run the code you provide to set up your scenario and run what you tried, they can offer tested answers.
September 21, 2015 at 9:00 am
Depending on the query there is a processing order of the rows of a table. It is not determined which is the first row to be processed and which is the last row to be processed.
So code should not depend on this.
Use a ORDER BY clause to determine in which order the table should be processed.
The code below uses a ORDER BY clause and a first and last row can be produced, but you should not depend on that this produces the same results everytime.
(The ORDER BY clause orders on a (SELECT NULL), so effectively the table is not ordered on anything).
--
-- Create a table.
--
select 3 as nr, 'a' as kar into ##SS
insert into ##SS values
(9, 'C'),
(15,'h'),
(7, 'e'),
(6, 'd'),
(12,'z'),
(4, 'b'),
(5, 'q')
select * from ##SS
--
-- Get the first row of a table. This might be 'any' row
--
;
With
A as (select
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL)) a_number, * from ##SS)
select * from a where a_number = (select min(a_number) from a)
--
-- Get the last row of a table. This might be 'any' row
--
;
With
A as (select
ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY (SELECT NULL)) a_number, * from ##SS)
select * from a where a_number = (select MAX(a_number) from a)
--
-- Get the highest and the lowest values.
--
select MAX(nr) as Max_nr, MIN(nr) as Min_nr, MAX(kar) as Max_kar, MIN(kar) as min_kar from ##SS
Ben
September 21, 2015 at 9:27 am
You overcomplicate things, Ben. It can all be done in a single query.
This won't solve the whole problem, but it can show how it could be done.
--
-- Create a table.
--
select 3 as id, 'a' as value into #SampleData
insert into #SampleData values
(9, 'C'),
(15,'h'),
(7, 'e'),
(6, 'd'),
(12,'z'),
(4, 'b'),
(5, 'q')
select *
from #SampleData
ORDER BY id
SELECT first_value,
last_value,
max_value
FROM
( SELECT TOP 1
value AS first_value,
MAX(value) OVER() AS max_value
FROM #SampleData
ORDER BY id) a,
( SELECT TOP 1
value AS last_value
FROM #SampleData
ORDER BY id DESC) b
GO
DROP TABLE #SampleData
September 22, 2015 at 1:58 am
drop table ##ss
select 3 as nr, 'a' as kar, 1 as groupingcode into ##SS
insert into ##SS values
(9, 'C',1),
(15,'h',1),
(7, 'e',1),
(6, 'd',1),
(12,'z',1),
(4, 'b',1),
(5, 'q',1)
select * from ##SS
--
-- Get the first row of a table. This might be 'any' row
--
;
Select top 1
min(nr) over (partition by 1 order by groupingcode desc),
max(nr) over (partition by 1 order by groupingcode desc),
first_value(nr) over (partition by 1 order by groupingcode desc),
last_value(nr) over (partition by 1 order by groupingcode desc)
from ##ss
just another idea to play with
:w00t:
September 22, 2015 at 2:14 am
Hello Luis
(and ITU_dk2012)
The first time I anwsered, the anwser got lost because my regular work interrupted and when 'posting the anwser' this was 'too late'. Second time I shortened the anwser.
The point was not the code, but that there is no defined first and/or last row.
The code showed how to get 'a first' and 'a last' row without sorting and without a cursor.
Ben
select top 1 * from ##SS
This could replace the first statement.
For the last row the code becomes more complicated.
But I used the same construction twice to limit the number of constructions.
September 22, 2015 at 6:53 am
Ben,
I agree completely with you in the sense that there's no way to define a first or last row without an ORDER BY. Rows in a table aren't organized in a sequential way like in a spreadsheet.
I still think that you overcomplicate things (and I probably did, too). 😀
WITH CTE AS(
SELECT *,
ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) row_num, --no real order for the rows. Could change at any moment.
COUNT(*) OVER() AS row_count
FROM #SampleData
)
SELECT MAX( CASE WHEN row_num = 1 THEN value END) AS first_value,
MAX( CASE WHEN row_num = row_count THEN value END) AS last_value,
MAX( value) AS first_value
FROM CTE;
Steve,
FIRST_VALUE and LAST_VALUE are good options, but this was posted in a SQL 2008 forum, so they might not apply.
September 22, 2015 at 7:26 am
DoH :ermm:
September 22, 2015 at 8:08 am
steve.tarry (9/22/2015)
drop table ##ssselect 3 as nr, 'a' as kar, 1 as groupingcode into ##SS
insert into ##SS values
(9, 'C',1),
(15,'h',1),
(7, 'e',1),
(6, 'd',1),
(12,'z',1),
(4, 'b',1),
(5, 'q',1)
select * from ##SS
--
-- Get the first row of a table. This might be 'any' row
--
;
Select top 1
min(nr) over (partition by 1 order by groupingcode desc),
max(nr) over (partition by 1 order by groupingcode desc),
first_value(nr) over (partition by 1 order by groupingcode desc),
last_value(nr) over (partition by 1 order by groupingcode desc)
from ##ss
just another idea to play with
:w00t:
Lose the expensive sort:
SELECT TOP 1
min(nr) OVER (),
max(nr) OVER (),
first_value(nr) OVER (ORDER BY (SELECT NULL)),
last_value(nr) OVER (ORDER BY (SELECT NULL))
FROM #ss
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply