June 19, 2012 at 5:53 am
Hi
Here is my table and sample data:
create table tab1(id int,oldId int, InsertedOn datetime)
insert into tab1 values (99,NULL,getdate()-7)
insert into tab1 values (17,14,getdate()-9)
insert into tab1 values (14,12,getdate()-14)
insert into tab1 values (12,null,getdate()-15)
Now i need to write a proc which when called like this:
exec MYProc 20--passed parameter id latest id
Should return:
idoldId
12NULL
1412
1714
2017
Since 20 is my latest ID, I should get the complete chain for that id. I was able to achieve it using loop and union etc., but i know it can be writen in a better way in single query.
Help me to get that single query...
thanks
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 19, 2012 at 6:02 am
Try this
select * from tab1
order by id
June 19, 2012 at 6:03 am
srikant maurya (6/19/2012)
Try thisselect * from tab1
order by id
Where is the passed parameter used in thsi query? it will return everything including id=99, which I dont want when I pass 20
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 19, 2012 at 6:08 am
Try this
create proc somename
(@paramname int
)
as
begin
select what ever
from some table
where id=@paramname
end
I sure you get the jist:w00t:
***The first step is always the hardest *******
June 19, 2012 at 6:16 am
Looks like you guys are missing my point. If I run this query, then I'll get only that row which has id=20.
What I want is complete zigzag chain of latest id 20 as I posted in my original question
SGT_squeequal (6/19/2012)
Try this
create proc somename
(@paramname int
)
as
begin
select what ever
from some table
where id=@paramname
end
I sure you get the jist:w00t:
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 19, 2012 at 6:18 am
id and oldid is one-to-one? or many-to-one?
June 19, 2012 at 6:29 am
if you want everything previous, then wouldn't it simply be less than or euql to @param?
create proc somename
(@paramname int
)
as
begin
select what ever
from some table
where id <= @paramname
end
or are you talking about a hierarchy?
Lowell
June 19, 2012 at 6:36 am
First you need to add in your missing row that is in your results, but not in the sample data. Then use a recursive cte:
USE tempdb
GO
DECLARE @tab1 TABLE (id int,oldId int, InsertedOn datetime)
insert into @tab1 values (99,NULL,getdate()-7)
insert into @tab1 values (17,14,getdate()-9)
insert into @tab1 values (14,12,getdate()-14)
insert into @tab1 values (12,null,getdate()-15)
insert into @tab1 values (20,17,getdate()-5)
;WITH cte (id, oldid)
AS (
SELECT id, oldid FROM @tab1 WHERE id = 20
UNION ALL
SELECT t1.id, t1.oldid
FROM @tab1 t1
INNER JOIN cte c1
ON c1.oldid = t1.id)
SELECT *
FROM cte
ORDER BY id
To make this a proc, change the 20 to a parameter.
Jared
CE - Microsoft
June 19, 2012 at 6:36 am
S_Kumar_S (6/19/2012)
HiHere is my table and sample data:
create table tab1(id int,oldId int, InsertedOn datetime)
insert into tab1 values (99,NULL,getdate()-7)
insert into tab1 values (17,14,getdate()-9)
insert into tab1 values (14,12,getdate()-14)
insert into tab1 values (12,null,getdate()-15)
Now i need to write a proc which when called like this:
exec MYProc 20--passed parameter id latest id
Should return:
idoldId
12NULL
1412
1714
2017
Since 20 is my latest ID, I should get the complete chain for that id. I was able to achieve it using loop and union etc., but i know it can be writen in a better way in single query.
Help me to get that single query...
thanks
Ok, I think I get what you want, you would want to chase the 20-17, then 17 to 14, then 14 to 12, then 12 to the NULL. Is the final entry always NULL in the old id?
Assuming it is, you need a temp table and a recursive call to get the chain, passing in the next "link" in the chain, all wrapped in a While Loop. Someone else might have a single statement approach, but I think this is a call for recursion. CTE might be an altarnative as well.
June 19, 2012 at 6:38 am
create table #tab1(id int,oldId int, InsertedOn datetime)
insert into #tab1 values (99,NULL,getdate()-7)
insert into #tab1 values (17,14,getdate()-9)
insert into #tab1 values (14,12,getdate()-14)
insert into #tab1 values (12,null,getdate()-15)
insert into #tab1 values (20,17,getdate()-8)
select distinct a.id,a.oldId
from #tab1 a
join #tab1 b on (a.oldid=b.Id) OR (b.id=a.Id)
where a.id<=20
June 19, 2012 at 6:42 am
CREATE PROC myproc
@param INT
as
begin
select @param, MAX(ID) from tab1 WHERE ID < @param
union
select ID, oldid from tab1 where ID < @param
end
June 19, 2012 at 6:47 am
Let me see if I can explain this better for the OP. It is irrelevant the value of the ID in terms of max, min, >, <... He wants to show the chain starting with a given ID. The data could just as well be:
DECLARE @tab1 TABLE (id int,oldId int, InsertedOn datetime)
insert into @tab1 values (99,NULL,getdate()-7)
insert into @tab1 values (59,1008,getdate()-9)
insert into @tab1 values (1008,2,getdate()-14)
insert into @tab1 values (2,null,getdate()-15)
insert into @tab1 values (20,59,getdate()-5)
The point is that the op wants to either pass or find an "id" and then look at the corresponding "oldid". Then find the "id" that matches that "oldid" and find ITS "oldid" all the way until the original record which will not have an "oldid"; i.e. oldid IS NULL.
Jared
CE - Microsoft
June 19, 2012 at 6:52 am
Here is the proc:
USE test
GO
CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)
insert into tab1 values (99,NULL,getdate()-7)
insert into tab1 values (17,14,getdate()-9)
insert into tab1 values (14,12,getdate()-14)
insert into tab1 values (12,null,getdate()-15)
insert into tab1 values (20,17,getdate()-5)
GO
CREATE PROC myProc
@param INT
AS
BEGIN
;WITH cte (id, oldid)
AS (
SELECT id, oldid
FROM tab1
WHERE id = @param
UNION ALL
SELECT t1.id, t1.oldid
FROM tab1 t1
INNER JOIN cte c1
ON c1.oldid = t1.id)
SELECT id, oldid
FROM cte
ORDER BY id
END
GO
EXEC myProc 20
Jared
CE - Microsoft
June 19, 2012 at 7:29 am
This is what I needed but why CTE? we can only use query used in this CTE, right?
SQLKnowItAll (6/19/2012)
Here is the proc:
USE test
GO
CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)
insert into tab1 values (99,NULL,getdate()-7)
insert into tab1 values (17,14,getdate()-9)
insert into tab1 values (14,12,getdate()-14)
insert into tab1 values (12,null,getdate()-15)
insert into tab1 values (20,17,getdate()-5)
GO
CREATE PROC myProc
@param INT
AS
BEGIN
;WITH cte (id, oldid)
AS (
SELECT id, oldid
FROM tab1
WHERE id = @param
UNION ALL
SELECT t1.id, t1.oldid
FROM tab1 t1
INNER JOIN cte c1
ON c1.oldid = t1.id)
SELECT id, oldid
FROM cte
ORDER BY id
END
GO
EXEC myProc 20
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
June 19, 2012 at 7:31 am
Sorry, I missed to see that you used CTE inside the query to make it nested.
Thanks
SQLKnowItAll (6/19/2012)
Here is the proc:
USE test
GO
CREATE TABLE tab1 (id int,oldId int, InsertedOn datetime)
insert into tab1 values (99,NULL,getdate()-7)
insert into tab1 values (17,14,getdate()-9)
insert into tab1 values (14,12,getdate()-14)
insert into tab1 values (12,null,getdate()-15)
insert into tab1 values (20,17,getdate()-5)
GO
CREATE PROC myProc
@param INT
AS
BEGIN
;WITH cte (id, oldid)
AS (
SELECT id, oldid
FROM tab1
WHERE id = @param
UNION ALL
SELECT t1.id, t1.oldid
FROM tab1 t1
INNER JOIN cte c1
ON c1.oldid = t1.id)
SELECT id, oldid
FROM cte
ORDER BY id
END
GO
EXEC myProc 20
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply