Mohan Rajesh Mohan Rajesh - 3 months ago 16
SQL Question

Optimized SQL Query to retrieve a Particular set of records from Microsoft SQL Server DB

I want to retrieve the set of data from a column in the table.
My Scenario is:
Iam having a table with name table1_data , In that table there is a Column with name "clm_Name", The data in the column is like this

a
b
c
a
b
c
a
b
a
b
c
a
a
b
c


I want to retrieve the data when a b c are in order if order changes it should not retrieve.(i.e, If we write a query on given data the output should be a b c a b c a b a b c a a b c) only the bolded letters should be shown in output.

Answer

If you have a column to sort you can do as follows:

DECLARE @Tbl TABLE (OrderId INT, val NVARCHAR(1))

INSERT INTO @Tbl        
VALUES  
(1,'a'),
(2,'b'),
(3,'c'),
(4,'a'),
(5,'b'),
(6,'c'),
(7,'a'),
(8,'b'),
(9,'a'),
(10,'b'),
(11,'c'),
(12,'a'),
(13,'a'),
(14,'b'),
(15,'c')


;WITH  CTE
AS
(   
    SELECT 
        *, 
        ROW_NUMBER() OVER (ORDER BY (SELECT OrderId)) RowId
    FROM @Tbl
), Result
AS 
(
    SELECT  
        CurrRow.RowId
    FROM
        CTE CurrRow LEFT JOIN 
        (SELECT CTE.val , CTE.RowId - 1 RowId FROM CTE) NextRow ON CurrRow.RowId = NextRow.RowId LEFT JOIN 
        (SELECT CTE.val , CTE.RowId + 1 RowId FROM CTE) PrivRow ON CurrRow.RowId = PrivRow.RowId
    WHERE
        PrivRow.val = 'a' AND 
        CurrRow.val = 'b' AND 
        NextRow.val = 'c'
)


SELECT 
    * 
FROM 
    CTE C
WHERE
    C.RowId IN ( 
                    SELECT Result.RowId FROM Result
                    UNION ALL
                    SELECT Result.RowId - 1 FROM Result
                    UNION ALL
                    SELECT Result.RowId + 1 FROM Result
                )
ORDER BY C.OrderId

Output:

OrderId val RowId
1       a   1
2       b   2
3       c   3
4       a   4
5       b   5
6       c   6
9       a   9
10      b   10
11      c   11
13      a   13
14      b   14
15      c   15