1

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

  • 1
    So you are expecting SQL Server to return the eventual results in the same order that they were inserted (& remember that that's a VALUES() set, not a bunch of individual inserts)? SQL Server doesn't keep track of insert order unless there is some column to do it. With a table variable it is relatively stable but it gets less predictable with a user table, so if you're dumbing it down and showing us a table variable when you're really using a user table in your application, show us the real table and let us know if it can be changed (and also if the cross apply input is really hard-coded). – Aaron Bertrand Dec 3 '15 at 14:20
  • Can you better explain the actual results you want? I don't know what "ordering of cross apply sequence" means - can you show it by listing the output rows in the order you want to see them? – Aaron Bertrand Dec 3 '15 at 14:24
4

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).

  • @Muflix To expand on this, please see #3 here: blogs.sqlsentry.com/aaronbertrand/… – Aaron Bertrand Dec 3 '15 at 14:14
  • 2
    (Though I don't think FirstName, LastName, VariableName, Value is the order they want. They're already getting that. It would seem to me that they want to remove row 4 and 6 from the original "ordered" result so that the results for FN1/LN1 are Variable1/D - Variable2/B - Variable3/A - Variable2/C. But they didn't show what "ordering of cross apply sequence" really means to them. – Aaron Bertrand Dec 3 '15 at 14:22
  • thank you, but Aaron is right I edited question for better understanding. – Muflix Dec 29 '15 at 16:20
1

Not sure I get what you are want here but it could be that you can use a sort order value in your table expression and then use that in an order by clause.

SELECT DISTINCT
    t.FirstName, t.LastName, v.VariableName, v.Value, v.Sortorder
FROM @Table as t
CROSS APPLY (
    VALUES    
        ('Variable1', t.Variable1, 1),
        ('Variable3', t.Variable3, 2),
        ('Variable2', t.Variable2, 3)
) AS v(VariableName, Value, Sortorder)
ORDER BY t.FirstName, t.LastName, v.Sortorder;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.