Ordering on every row...excep the first row
-
I have some data that I would like to sort, but there is one line I would like to show up at the top of the data regardless of sorting.
I can do this by adding another column, giving that row a 0 and every other row a 1, and then sorting on that. However, I was wondering if there was a more elegant way to do this.
-
Hard to provide an answer to such a vague question. It would help to know more about the data so that we can provide an answer to your question.
-
you can use a case statement in the ORDER BY clause:
ORDER BY
CASE
WHEN <something that makes the first row first>
THEN 0
ELSE 1
END,
OtherColumnToSortBy
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Yes you can do conditional sorting. For example, to put 'dunlap' at the top and sort all the rest values:
select * from t1
order by case
when c1 <> 'dunlap' then c1 end
-
There is another way, less common than that suggested by Lowell, which suits some circumstances.
It has the advantage of being able to use any useful index that might exist.
Sample data:
DECLARE @Sample
TABLE (
row_id INTEGER NOT NULL PRIMARY KEY,
data VARCHAR(10) NOT NULL
);
INSERT @Sample (row_id, data) VALUES (1, 'Apple');
INSERT @Sample (row_id, data) VALUES (2, 'Banana');
INSERT @Sample (row_id, data) VALUES (3, 'Cherry');
INSERT @Sample (row_id, data) VALUES (4, 'Date');
INSERT @Sample (row_id, data) VALUES (5, 'Elderberry');
INSERT @Sample (row_id, data) VALUES (6, 'Fig');
Code:
SELECT T1.row_id,
T1.data
FROM (
SELECT S.row_id,
S.data
FROM @Sample S
WHERE row_id = 3
) T1
UNION ALL
SELECT T2.row_id,
T2.data
FROM (
SELECT TOP (9223372036854775807)
S.row_id,
S.data
FROM @Sample S
WHERE row_id <> 3
ORDER BY
row_id
) T2;
Results:
row_id data
3 Cherry
1 Apple
2 Banana
4 Date
5 Elderberry
6 Fig
Execution plan:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I tried the order by idea but I'm coming across an error. Here's some sample code.
Select 'BEGDOC' as BEGDOC
UNION ALL
SELECT BEGDOC FROM TABLE 1
ORDER BY CASE WHEN BEGDOC = 'BEGDOC' THEN 1 ELSE 0 END
It tells me I have an "Invalid column Name 'BEGDOC'"
The problem is definately in the order by statement, removing it removes any errors.
Anyone have an idea what might be causing this?
-
huston.dunlap (3/16/2010)
Anyone have an idea what might be causing this?try this
select * from(Select 'BEGDOC' as BEGDOC
UNION ALL
SELECT 'BEGDOC' FROM table ) t
ORDER BY CASE WHEN t.BEGDOC = 'BEGDOC' THEN 1 ELSE 0 END
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
huston.dunlap (3/16/2010)
I tried the order by idea but I'm coming across an error. Here's some sample code.Anyone have an idea what might be causing this?
Compare your code with that posted by Lowell and me.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply