CODEWITHSUNDEEP
linqsql-server-2008sql-order-by
user1
user1

Reputation: 1065

sort only particular rows sql server

I have six rows like

col1     col2
--------------
Apple    120
XApple   140 
Banana   130
Xbanana  150
Car      110
XCar     160

I would like to sort these rows on col2 but leave the rows with 'X' alone.

so after sorting the rows should be like

col1     col2
--------------
Car      110
Apple    120
Banana   130
XCar     160
XApple   140
Xbanana  150

meaning, the rows with car apple and banana should be sorted but the rows with xcar, xapple and xbanana should be left alone and just be appended at the end. I tried

select * 
from table
where col1 not like 'X%' order by col2
union
select * 
from table
where symbol like 'X%'

but sql server doesn't allow that. Could anybody point me to the right direction or tell me that this is not possible?

PS: any LINQ solution will also be fine.

thanks

Upvotes: 0

Views: 284

Answers (3)

Robert McKee
Robert McKee

Reputation: 21477

Order by whether the first character of col1 is 'X' or not, and then by col2.

Example:

SELECT *
FROM table
ORDER BY CASE WHEN col1 LIKE 'X%' THEN 1 ELSE 0 END,col2

Although, this doesn't leave the LIKE 'X%' rows unordered, neither did your example.

Upvotes: 1

HABO
HABO

Reputation: 15816

There is no order without an explicit ORDER BY clause. While you can use UNION to group the rows, you cannot guarantee that the order of the unordered rows is stable. See here.

The following will split the list into two groups of rows, but each will be sorted by Col1:

select Col1, Col2
  from (
    select Col1, Col2, 1 as PleaseSort
      from MyTable
      where Col1 not like 'X%'
    union
    select Col1, Col2, 0
      from MyTable
      where Col1 like 'X%' ) as PlaceHolder
  order by PleaseSort desc, Col1

Upvotes: 0

Steph Locke
Steph Locke

Reputation: 6146

If you know the sort of upper limit for how many x rows you'll get then you could do something like....

select * from (select top 5000 col1 from #tmp order by col1) a
union
select col1 from #tmp

Upvotes: 0

Related Questions