December 23, 2014 at 8:32 am
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
December 23, 2014 at 8:39 am
No worries,
I think I found it......LAG function should do the trick. Will test.
December 23, 2014 at 8:52 am
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...
December 23, 2014 at 9:00 am
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
December 23, 2014 at 9:14 am
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 pos
FROM master.sys.columns
)
SELECT
cte1.*,
CASE WHEN cte1.object_id = cte2.object_id THEN 'same table' ELSE 'new table' END
FROM cte cte1
LEFT OUTER JOIN cte cte2 ON cte2.pos=cte1.pos-1
December 23, 2014 at 9:19 am
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