How to Sort in Order as Entered in SQL Server

How to SORT in order as entered in SQL Server?

Here is an in-line approach

Example

Declare @List varchar(max)='212345, 312345, 145687, 658975, 256987, 365874, 568974, 124578, 125689'

Select A.AccountNumber 
      ,A.EndDate
 From  Accounts A
 Join (
        Select RetSeq = Row_Number() over (Order By (Select null))
              ,RetVal = v.value('(./text())[1]', 'int')
        From  (values (convert(xml,'<x>' + replace(@List,',','</x><x>')+'</x>'))) x(n)
        Cross Apply n.nodes('x') node(v)
      ) B on A.AccountNumber = B.RetVal
 Order By B.RetSeq

EDIT - the subquery Returns

RetSeq  RetVal
1       212345
2       312345
3       145687
4       658975
5       256987
6       365874
7       568974
8       124578
9       125689

SQL WHERE IN (...) sort by order of the list?

Select ID list using subquery and join with it:

select t1.*
from t1
inner join
(
  select 1 as id, 1 as num
  union all select 5, 2
  union all select 3, 3
) ids on t1.id = ids.id
order by ids.num

UPD: Code fixed

SQL Server sort data with Order By according to current date

Consider a conditional sort:

order by
    case 
        when dueDate = cast(getdate() as date) then 0
        when dueDate > cast(getdate() as date) then 1
        else 2
    end,
    dueDate

The first expression in the order by clause gives priority to record on the current date, then to future records. The second sorting criteria sorts then subsets by dueDate.

Preserving ORDER BY in SELECT INTO

What for?

Point is – data in a table is not ordered. In SQL Server the intrinsic storage order of a table is that of the (if defined) clustered index.

The order in which data is inserted is basically "irrelevant". It is forgotten the moment the data is written into the table.

As such, nothing is gained, even if you get this stuff. If you need an order when dealing with data, you HAVE To put an order by clause on the select that gets it. Anything else is random - i.e. the order you et data is not determined and may change.

So it makes no sense to have a specific order on the insert as you try to achieve.

SQL 101: sets have no order.

What's the best way to store sort order in SQL?

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex problem that is generally not worth the amount of time it takes to implement because almost no one ever uses that system once it's in place (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated in terms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

I did this once in a proposal writing application because we needed to be able to sort the parts and tasks on the proposal in the order we thought would be most impressive to the customer. Even then, we had to institute a default order, so that they only need to move around the two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

Return rows in the exact order they were inserted

As others have already written, you will not be able to get the rows out of the link table in the order they were inserted.

If there is some sort of internal ordering of the rows in one or both of the tables that this link table is joining, then you can use that to try to figure out when the link table rows have been created. Basically, they cannot have been created BEFORE both of the rows containing the PK:s have been created.

But on the other hand you will not be able to find out how long after they have been created.

If you have decent backups, you could try to restore one or a few backups of varying age and then try to see if those backups also contains this strange behaviour. It could give you at least some clue about when the strangeness has started.

But the bottom line is that using just a select, there is now way to get the row out of a table like this in the order they were inserted.

Using a sort order column in a database table

Update product set order = order+1 where order >= @value changed

Though over time you'll get larger and larger "spaces" in your order but it will still "sort"

This will add 1 to the value being changed and every value after it in one statement, but the above statement is still true. larger and larger "spaces" will form in your order possibly getting to the point of exceeding an INT value.

Alternate solution given desire for no spaces:

Imagine a procedure for: UpdateSortOrder with parameters of @NewOrderVal, @IDToChange,@OriginalOrderVal

Two step process depending if new/old order is moving up or down the sort.

If @NewOrderVal < @OriginalOrderVal --Moving down chain 

--Create space for the movement; no point in changing the original 
    Update product set order = order+1 
    where order BETWEEN @NewOrderVal and @OriginalOrderVal-1;

end if

If @NewOrderVal > @OriginalOrderVal --Moving up chain

--Create space  for the momvement; no point in changing the original  
  Update product set order = order-1 
  where order between @OriginalOrderVal+1 and @NewOrderVal
end if

--Finally update the one we moved to correct value

    update product set order = @newOrderVal where ID=@IDToChange;

Regarding best practice; most environments I've been in typically want something grouped by category and sorted alphabetically or based on "popularity on sale" thus negating the need to provide a user defined sort.

---

Related Topics