CODEWITHSUNDEEP
sql-servert-sqlsql-order-by
response.write
response.write

Reputation: 175

Use ORDER BY in SQL Server starting from row 2 onwards only

I have a table that always returns multiple of rows, in which I use those rows as value in a combo box in VB.NET. In those rows, the 1st row is the data that is set to be as default value. Now, the rest of the rows must be ordered alphabetically to make it easier to read. I'm trying to use ROW_NUMBER() right now, is there a concise way for me to do this.

Using this structure as basis.

tbl_Sample
col_ID - int
col_description - varchar(30)

with these datas present

col_ID  |  col_description
--------------------------
1       |  Default_Value
2       |  a_value2
3       |  a_value2
4       |  a_value5
5       |  a_value1
6       |  a_value3

i want to have a query that returns something like this

col_ID  |  col_description
--------------------------
1       |  Default_Value
5       |  a_value1
2       |  a_value2
3       |  a_value2
6       |  a_value3
4       |  a_value5

as for the query, as I said I'm testing up ROW_NUMBER() along with OVER and ORDER BY, since ordering it by col_description will not work since the arrangement of the descriptions in alphabetical order will alter the Default_Value's row number which must remain in row 1.

Upvotes: 0

Views: 252

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82524

Actually, I see no reason to use row_number() here. you can use the case in the order by clause directly:

SELECT col_ID, col_description
FROM tbl_Sample
ORDER BY CASE WHEN col_description = 'Default_Value' THEN 0 ELSE 1 END, col_description

Upvotes: 1

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

If you want to GROUP BY column, using PARTITION BY, like this:

SELECT *, ROW_NUMBER()
OVER(PARTITION BY col_description ORDER BY CASE WHEN col_description = 'Default_Value' THEN 0 ELSE 1 END ASC, col_description ASC) AS RN
FROM tbl_Sample

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

row_number() over(order by case when col_description = 'Default_Value' then 0 else 1 end ASC, col_description ASC)

Upvotes: 2

Related Questions