SQL code to get next variable in table with different value

Question

I need to find a way in SQL Server 2014 Management Studios to find the next unique value in a column that shares the value of a different column.

So for example below I would want my results to be

Column 1 - A
Column 2 - 1
Column 3 - 4

As that is the first time that A has unique values in column 2 and 3

 Column1 | Column2 | Column3
---------+---------+---------
|    A   |    X    |   1   |
|    A   |    X    |   2   |
|    B   |    Y    |   3   |
|    A   |    Z    |   4   |

Query:

SELECT 
    Column1,
    LEAD(Column3) OVER (PARTITION BY Column2 ORDER BY Column3) AS FindValue
FROM 
    Table

As best as I can.. words are not coming to me at the moment.. I would like to display each unique value from column1 (so for row 1 - A) then the first the first record from Column 3 based on the first unique record of Column 2 ( so 1, as its the first value to align to X), then finally the first record from column 3 - that shares the value from column 1, but is the next value from column2. (as in Z comes after X) — Chad Collings, Jan 04 '19 at 05:07
Please, show the exact output you need (in the form of table). For now it's not clear what you need. — JohnyL, Jan 04 '19 at 09:31

wojtg · Answer 1 · 2019-01-04T18:37:45.703

If I understand it correctly I would try something like this:

-- first we find minimum values for column1, column2 variations
WITH min_values AS (
    SELECT 
        column1,
        column2,
        min(column3) AS min_value
    FROM 
        table
    GROUP BY 1,2
)
-- then we find bottom 2 values for column1
,bottom_2 AS (
    SELECT
        column1,
        min_value,
        row_number() OVER (PARTITION BY column1 ORDER BY min_value ASC) AS rn
    FROM
        min_values
)
-- THEN we JOIN results INTO single record
    SELECT 
        b1.column1, b2.min_value, b1.min_value
    FROM
        bottom_2 b1
    JOIN
        bottom_2 b2 ON b1.column1 = b2.column1 AND b2.rn < b1.rn
    WHERE b1.rn <= 2

I just checked comments above and would like to add some notes. If you want to find next value ordered by column2 then you have to change order by from min_value to column2 in row_number() line. Otherwise, if you are looking for next inserted value then you need a timestamp or some kind of id.

SQL code to get next variable in table with different value

1 Answers1