Query Needed | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Query Needed

Hi All, I have a table and inside this table I have million of records. The table structure is like Dept Np Auth No. Ident No.
1001 200101 1
1001 200102 1
1001 200103 2
1001 200104 1
1001 200105 1
1001 200106 20
1001 200107 2
1001 200108 2
1001 200109 2
1001 200110 20
I need to fetch those records where Ident No. values being changing. Like If first two rows have 1 in Ident No. then first row and after that the row where Ident no. 2 and again first row of Ident no. having again 1. I have used cusor but it is very much time consuming. Can anyone suggest me any logic for this query. Thanks,
Shekhar Mishra "No passion in the world is equal to the passion to alter someone else’s draft."
Is this 3 columns (Dept + Np + Ident) where Np is a unique key in ascending order? And we’re disregarding the Dept column? Then you can use a self-join with a couple of expressions in the ON clause: SELECT t2.*
FROM tbl t1
INNER JOIN tbl t2
ON t1.Np = (t2.Np – 1) AND t1.Ident <> t2.Ident
Only problem with that is that it’s skipping the first row … SELECT tbl.*
FROM tbl
WHERE tbl.Np = (SELECT MIN(t.Np) FROM tbl t)
UNION ALL
SELECT t2.*
FROM tbl t1
INNER JOIN tbl t2
ON t1.Np = (t2.Np – 1) AND t1.Ident <> t2.Ident
ORDER BY Np
Thanks Adriaan
"No passion in the world is equal to the passion to alter someone else’s draft."
Also, the previous version works only if there are no gaps in the series of values on the Np column. If there are gaps, then you could use a derived table with a correlated subquery, instead of the self-join … SELECT tbl.*
FROM tbl
WHERE tbl.Np = (SELECT MIN(t.Np) FROM tbl t)
UNION ALL
SELECT t2.*
FROM tbl t1
INNER JOIN
(SELECT x1.Np, x1.Ident, (SELECT MAX(x2.Np) FROM tbl x2 WHERE x2.Np < x1.Np) PreviousNp
FROM tbl x1) t2
ON t1.Np = t2.PreviousNp AND t1.Ident <> t2.Ident
ORDER BY Np … but this can be a time-consuming query, which may even be outperformed by a cursor-based solution.
]]>