Click here to Skip to main content
15,886,725 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
how to get particular row at first remaining records in same order

ex:

table

a b c
------
2 seed 10
3 kadd 12
1 mass 32
4 ssds 55
---------

i want first row is b=mass
Posted
Comments
hypermellow 8-May-15 6:54am    
select a,b,c from table order by a; ... or am I miss understanding the question?

Posting this solution as another has already been posted but I think an alternative might be required.

At first sight it looks as if you just want the table to be ordered by column [a] in which case you need (nod to @hypermellow)
SQL
select a, b, c from [table] order by a
which will produce
1 mass 32
2 seed 10
3 kadd 12
4 ssds 55
However your wording
Quote:
how to get particular row at first remaining records in same order
suggests that you might want something like
SQL
with CTE AS (
    select a, b, c, 1 as rownum from [table] where b = 'mass'
    UNION ALL
    select a, b, c, 1 + ROW_NUMBER() OVER (ORDER BY b)
    FROM [table] where b <> 'mass'
)
SELECT a, b, c FROM CTE order by rownum
which will produce results
1	mass	32
3	kadd	12
2	seed	10
4	ssds	55
The point of interest here is that I have manually assigned a rownum of 1 to the record I want first, then allowed sql to generate row numbers for me based on whichever order is required (I used column [b] just to highlight the difference) - note that I add 1 to the generated row number to ensure the sub-query doesn't end up with a rownum=1 to clash with my first query.
I then just UNION the two queries.

If you did only want a single record returned (in either case) then use TOP 1 as suggested by Amaan23 (but your wording implies that this is not what you want)
 
Share this answer
 
Comments
Richard Deeming 8-May-15 7:54am    
It would be easier to use a CASE statement in the ORDER BY clause:
SELECT a, b, c FROM [table] ORDER BY CASE WHEN b = 'mass' THEN 0 ELSE 1 END, b;
CHill60 8-May-15 7:57am    
Do you know what, you're not wrong! How did I miss that? :facepalm:
Had a previous CTE query open in Sql and got blinkered.
Post as solution and I'll upvote
A simpler alternative to CHill60's answer is to use a CASE statement in the ORDER BY clause:
SQL
SELECT 
    a, 
    b, 
    c 
FROM 
    [table] 
ORDER BY 
    CASE 
        WHEN b = 'mass' THEN 0 
        ELSE 1 
    END, 
    b
;
 
Share this answer
 
Comments
CHill60 8-May-15 8:05am    
+5 as promised!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900