Sql-server – How to keep row ordering with DISTINCT command or how to add row number to cross apply

cross-applyorder-bysql servert-sql

I have problem that after distinct, order direction of Variable column change.

Here is example

Data Source

DECLARE @Table TABLE  
(
    FirstName NVARCHAR(255),
    LastName NVARCHAR(255),
    Variable1 NVARCHAR(255),
    Variable2 NVARCHAR(255),
    Variable3 NVARCHAR(255)
)

INSERT INTO @Table VALUES 

('FirstName 1','LastName 1', 'D', 'B','A'),
('FirstName 1','LastName 1', 'D', 'C','A'),
('FirstName 2','LastName 2', 'A', 'B','C')

First select with proper ordering (order is done by Cross Apply sequence)

/* Ordering is okay, but table containts duplicates */
SELECT
    FirstName, LastName, v.VariableName, v.Value 

FROM @Table
CROSS APPLY (
    VALUES    
        ('Variable1',Variable1),
        ('Variable2',Variable2),
        ('Variable3',Variable3)
) AS v(VariableName, Value)

enter image description here

Second select is reordered by Distinct command

/* Ordering is okay, but table containts duplicates */
SELECT DISTINCT
    FirstName, LastName, v.VariableName, v.Value 

FROM @Table
CROSS APPLY (
    VALUES    
        ('Variable1',Variable1),
        ('Variable2',Variable2),
            ('Variable3',Variable3)
) AS v(VariableName, Value)

enter image description here

What I want

I need second select but with ordering of cross apply sequence. I thought about adding row number for every person but I don't know how to do it

By "cross apply sequence" I mean ordering by cross apply order in code (ordering by syntax). I don't know how to describe it better, therefore I attached an image.

enter image description here

Best Answer

Even with your first query the output order is in no way guaranteed. Unless a specific ordering is demanded via an ORDER BY clause the database is free to hand you the results in any order it see fit. For simple queries that can use an index it may look like the order is guaranteed because the output will be in the order of which ever index is used as the main seek/scan, but for anything more complex than a single table select there will be at least two ways to do it and the one the query planner chooses may change over time as the balance of data in your tables changes.

If you want a specific order, you must specify it in an ORDER BY clause.

In your this case ORDER BY FirstName, LastName, VariableName, Value seems to be what you want.

You don't mention caring if Value is in any particular order. If you don't care about that then go with ORDER BY FirstName, LastName, VariableName instead just in case the engine would need to do extra work to enforce the extra ordering detail. In you example data this would mean the rows FN1/LN1/V2/B and FN1/LN1/V2/C would come out in arbitrary order (i.e. sometimes B will be first, sometimes C will).

Related Question