Clever Geek Handbook

Join column that contains the previous maximum

Ok, let's say I have a table with a "code" field. The request select code from mytable group by code

returns:

code
EM010
EM100
EM101
EM105
EM106
...

I need to add a column (with a join or whatever) that will contain the next highest code. The above has addressed:

code    code2
EM010   null (or blank or wtvr)
EM100   EM010
EM101   EM100
EM105   EM101
EM106   EM105
...     ...

I would provide what I have tried, but it is messy and I continue to realize that I am wrong.

btw i work in vfp so technically tsql but i should be able to convert something else so i don't mind if i get answers that haven't been tested in tsql.

+3


source to share


2 answers


Try



DECLARE @T TABLE (Code VARCHAR(5))
INSERT @T VALUES ('EM010'), ('EM100'), ('EM101'), ('EM105'), ('EM106')

SELECT  T1.Code,
        (   SELECT  MAX(Code)
            FROM    @T t2
            WHERE   t1.Code > t2.Code
        ) AS Code2
FROM    @T t1
+1


source


Try the following:

WITH T1 AS
(
    SELECT code, ROW_NUMBER() OVER (ORDER BY code) AS rn FROM
    (
        SELECT DISTINCT code FROM mytable
    )
)
SELECT T1.code, T2.code AS code2
FROM T1
LEFT JOIN T1 AS T2
ON T1.rn = T2.rn + 1


By the way, in SQL Server 2012 you can use the LAG analytic function which does what you want. I'm not sure if Visual Fox Pro supports it.

+4


source






More articles:


All Articles