SQL select distinct data without ordering the column

Question

I have table which contains data as below:

DECLARE @CheckList TABLE
                   (  
                        RowNumber INT IDENTITY(1,1) INT, 
                        CheckId INT, 
                        Treat INT 
                   )  
INSERT INTO @CheckList 
VALUES (1, 1, 1), (1, 3, 1), (1, 2, 1), (1, 1, 1),
       (1, 3, 1), (1, 2, 1), (1, 1, 3), (1, 3, 3),
       (1, 2, 3);

SELECT DISTINCT Id, CheckId 
FROM @CheckList
WHERE Id = 1

Where I want to select the data from this table as

   Id   CheckId
   ------------
    1   1
    1   3
    1   2

I want the same order of the check id which is available in table. Can you help me with this?

Are you sure the second Treat set is (1,1,1)? or it is (1,1,2) ? — Arulkumar
– Arulkumar, Commented Jan 7, 2020 at 7:41
this is sample data but i want in same order which is available in table. i.e. 1 3 2 — Lifewithsun
– Lifewithsun, Commented Jan 7, 2020 at 7:41
Are you missing a column? Your SQL is not valid. There is no id column? How you are inserting data in identity column? — gotqn
– gotqn, Commented Jan 7, 2020 at 7:49

Serg · Accepted Answer · 2020-01-07 07:54:02Z

7

Retain original ordering by RowNumber identity column

select Id,CheckId
from(
   select distinct Id,CheckId, min(rownumber) over(partition by Id,CheckId) rn
   from @CheckList
   where Id=1
   ) t
order by rn;

Db fiddle

edited Jan 7, 2020 at 7:54

answered Jan 7, 2020 at 7:41

Serg

22.9k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Arpit Srivastava · Accepted Answer · 2020-01-08 05:58:46Z

-1

you can check this

DECLARE @CheckList TABLE
               (  Id INT ,  CheckId INT, Treat INT )  
INSERT INTO @CheckList 
VALUES (1, 1, 1), (1, 3, 1), (1, 2, 1), (1, 1, 1),(1, 3, 1), (1, 2, 1), (1, 1, 3),(1,3,3), (1, 2, 3);

SELECT DISTINCT Id, CheckId 
FROM @CheckList
WHERE Id = 1

answered Jan 8, 2020 at 5:58

Arpit Srivastava

92 bronze badges

Collectives™ on Stack Overflow

SQL select distinct data without ordering the column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related