I have a SQL table name category.here is the structure.
slno Category Uppercategory
1 Mouse Computer
2 Computer Electronics
3 Electronics END
4 END -
Here the mouse in the uppercategory Computer and then the Computer in the uppercategory Electronics.and the Electronics is the last uppercategory because the Electronics having the uppercategory ENd.I need to get the last category(Electronics)which the uppercatergory is END. I tried some code but not get the result.here is my code.
USE [Database1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[kt_category]
@Dcat AS NVARCHAR(250)
AS
DECLARE @tmp TABLE (cater NVARCHAR(255))
BEGIN
INSERT INTO @tmp
SELECT UPPERCATEGORY FROM CATEGORY where CATEGORY=@Dcat
while( SELECT UPPERCATEGORY FROM CATEGORY )= 'END'
RETURN
END
You can also use a recursive query to achieve this.
;WITH R
AS (SELECT *,
1 AS Level
FROM CATEGORY
WHERE Category = @Dcat
UNION ALL
SELECT C.*,
R.Level + 1
FROM CATEGORY C
JOIN R
ON R.Uppercategory = C.Category)
SELECT TOP 1 *
FROM R
ORDER BY Level DESC
You could also use
SELECT TOP 1 *
FROM R
WHERE Uppercategory = 'End'
ORDER BY Level
as the final query. This has closer semantics to the code in your question.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With