CODEWITHSUNDEEP
sqlsql-server
Jaydeep Shil
Jaydeep Shil

Reputation: 1959

SQL Server : select all rows from a column after the 1st row with matching value from the same column

Table1 -
ID | Name
--------
1  | Jos
2  | Tim
3  | Karl
4  | Joe
5  | Tom

Output I am looking for

Name
Tom
Jos
Tim
Karl
Joe

so the output should consist the matching and non-matching values, but the matching value in the first row.

I have tried something like below but I am not able to get the matching value in the first row.

select distinct Name
from(
SELECT Name
FROM table1
WHERE Id = 5
UNION SELECT Name
FROM table1) temp

Upvotes: 0

Views: 670

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

The @Juergen answer +1 is what I would probably use, but your union approach might even perform well, so here is how you can make it work:

SELECT name
FROM
(
    SELECT name, 1 AS ord FROM table1 WHERE id = 5
    UNION ALL
    SELECT name, 2 FROM table1 WHERE id <> 5
) t
ORDER BY ord;

The trick here is to introduce a computed column ord which keeps track of whether a name matches and should appear first.

Upvotes: 0

juergen d
juergen d

Reputation: 204756

select name
from your_table
order by case when id = 5 
              then 1 
              else 2 
         end, 
         id

Upvotes: 4

Related Questions