sql select to start with a particular record

Question

Is there any way to write a select record starting with a particular record? Suppose I have an table with following data:

 SNO    ID  ISSUE
 ----------------------
   1    A1  unknown
   2    A2  some_issue
   3    A1  unknown2
   4    B1  some_issue2
   5    B3  ISSUE4
   6    B1  ISSUE4

Can I write a select to start showing records starting with B1 and then the remaining records? The output should be something like this:

4    B1  some_issue2
6    B1  ISSUE4
1    A1  unknown
2    A2  some_issue
3    A1  unknown2
5    B3  ISSUE4

It doesn't matter if B3 is last, just that B1 should be displayed first.

Answer 1

Couple of different options depending on what you 'know' ahead of time (i.e. the id of the record you want to be first, the sno, etc.):

Union approach:

select   1 as sortOrder, SNO, ID, ISSUE
from     tableName
where    ID = 'B1'
union all
select   2 as sortOrder, SNO, ID, ISSUE
from     tableName
where    ID <> 'B1'
order by sortOrder;

Case statement in order by:

select   SNO, ID, ISSUE
from     tableName
order by case when ID = 'B1' then 1 else 2 end;

You could also consider using temp tables, cte's, etc., but those approaches would likely be less performant...try a couple different approaches in your environment to see which works best.

Answer 2

select * from table1
where id = 'B1'
union all
select * from table1
where id <> 'B1'

Answer 3

Assuming you are using MySQL, you could either use IF() in an ORDER BY clause...

SELECT SNO, ID, ISSUE FROM table ORDER BY IF( ID = 'B1', 0, 1 );

... or you could define a function that imposes your sort order...

DELIMITER $$
CREATE FUNCTION my_sort_order( ID VARCHAR(2), EXPECTED VARCHAR(2) )
RETURNS INT
BEGIN
  RETURN IF( ID = EXPECTED, 0, 1 );
END$$
DELIMITER ;

SELECT SNO, ID, ISSUE FROM table ORDER BY my_sort_sort( ID, 'B1' );