previous or next value
-
Hi all,
I'm wondering if it's possible to do the following in T-SQL but do not know if it is or what the syntax would be:
if fieldname = previous rows fieldname then xxxxxxx else yyyyyyy
many thanks
-
No worries,
I think I found it......LAG function should do the trick. Will test.
-
It depends on the SQL version you're using...
If you use SS2K8 (as indicated by the forum you posted in), LAG is not available...
-
yep I just found realised that LAG is not used in SQL 2k8
any other ideas of how to easily achieve this??
I basically want to say :
CASE
WHEN Fieldname = previous Fieldname and Fieldname2 = previous Fieldname2 THEN xxxxxxx
ELSE yyyyyyy
END
any help would be appreciate it.
Thanks
-
Based on the relational theory of RDBMS there's no "previous" row.
Whether a row is considered as "previous" or "next" depends on the order of rows.
What you could do is to use ROW_NUMBER() OVER(ORDER BY ...) to get a "numbered list" and perform a self-join.
Something like this (the last column will indicate a "new table" if the object_id is different than the one in the previous row of the result set):
WITH cte AS(SELECT object_id, name, ROW_NUMBER() OVER(ORDER BY Object_id, column_id) as posFROM master.sys.columns)SELECTcte1.*,CASE WHEN cte1.object_id = cte2.object_id THEN 'same table' ELSE 'new table' ENDFROM cte cte1LEFT OUTER JOIN cte cte2 ON cte2.pos=cte1.pos-1 -
Many thanks for that, will give it a bash
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply