November 30, 2011 at 12:32 pm
Hi all, I have these data:
/ *
ID id_part ord lev
1606 1513852 183 2
1606 1513853 184 3
1606 1513854 185 4
1606 1513855 186 4
1606 1513856 187 3
1606 1513857 188 4
1606 1513862 193 2
1606 1513863 194 3
* /
ID->ID
id_part-> ID
ord->identirt
lev->hierarchy of records.
*input parameter: id_part
What I need to get the first order [ORD] and the last, depending on the level, if my input parameter is 1513852 id_part the level of that is 2,the result would: [ORD FIRST:183] and [ORD LAST:188] because the after ORD 188 there other recort with the same level it was at the entrance (2).
Sorry for my english.
Thanks.
____________________________________________________________________________
Rafo*
November 30, 2011 at 1:56 pm
Hi,
attached is one way to solve this "puzzle" (assuming I understand the requirement correctly).
As a side note: please note how I posted the sample data in a ready to use format. That makes it easier for us to focus on the solution rather than preparing the sample data. 😉
DECLARE @tbl TABLE
(
ID INT, id_part INT, ord INT, lev int
)
INSERT INTO @tbl
SELECT 1606,1513852,183,2 UNION ALL
SELECT 1606,1513853,184,3 UNION ALL
SELECT 1606,1513854,185,4 UNION ALL
SELECT 1606,1513855,186,4 UNION ALL
SELECT 1606,1513856,187,3 UNION ALL
SELECT 1606,1513857,188,4 UNION ALL
SELECT 1606,1513862,193,2 UNION ALL
SELECT 1606,1513863,194,3
DECLARE @input INT
SET @input = 1513852
;
WITH cte_lev AS
(
SELECT id_part,lev,ord
FROM @tbl
WHERE id_part = @input
),
cte_next_id_part AS
(
SELECT TOP 1 sub.ord AS [ORD FIRST], t.id_part AS next_id_part
FROM @tbl t
CROSS APPLY (SELECT id_part,lev,ord FROM cte_lev) sub
WHERE t.id_part > sub.id_part AND t.lev = sub.lev
ORDER BY t.ord DESC
)
SELECT TOP 1 sub2.[ORD FIRST], t.ord [ORD LAST]
FROM @tbl t
CROSS APPLY (SELECT [ORD FIRST],next_id_part FROM cte_next_id_part) sub2
WHERE t.id_part < sub2.next_id_part
ORDER BY t.ord DESC
November 30, 2011 at 2:40 pm
Thanks dude, but i got a problem with ur example.
Check it:
DECLARE @tbl TABLE
(
ID INT, id_part INT, ord INT, lev int
)
INSERT INTO @tbl
SELECT 1606,1513852,183,2 UNION ALL
SELECT 1606,1513853,184,3 UNION ALL
SELECT 1606,1513854,185,2 UNION ALL
SELECT 1606,1513855,186,4 UNION ALL
SELECT 1606,1513856,187,3 UNION ALL
SELECT 1606,1513857,188,4 UNION ALL
SELECT 1606,1513862,193,2 UNION ALL
SELECT 1606,1513863,194,3
DECLARE @input INT
SET @input = 1513852
;
WITH cte_lev AS
(
SELECT id_part,lev,ord
FROM @tbl
WHERE id_part = @input
),
cte_next_id_part AS
(
SELECT TOP 1 sub.ord AS [ORD FIRST], t.id_part AS next_id_part
FROM @tbl t
CROSS APPLY (SELECT id_part,lev,ord FROM cte_lev) sub
WHERE t.id_part > sub.id_part AND t.lev = sub.lev
ORDER BY t.ord DESC
)
SELECT TOP 1 sub2.[ORD FIRST], t.ord [ORD LAST]
FROM @tbl t
CROSS APPLY (SELECT [ORD FIRST],next_id_part FROM cte_next_id_part) sub2
WHERE t.id_part < sub2.next_id_part
ORDER BY t.ord DESC
And the result is:
FIRST: 183
LAST: 188
if you see the last is wrong cuz the 3 record has lev 2.
the correct LAST would be 184
THANKS
____________________________________________________________________________
Rafo*
November 30, 2011 at 2:54 pm
First of all: don't "Dude" me. This is a professional forum and not a kids chatroom.
Regarding the error:
Change ORDER BY DESC to ORDER BY ASC in cte_next_id_part
November 30, 2011 at 3:01 pm
Okay, sorry for that, i appreciate ur help.
____________________________________________________________________________
Rafo*
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply