CODEWITHSUNDEEP
phpmysql
Roshan Dandgavhal
Roshan Dandgavhal

Reputation: 559

Display all record from table with perticular row at top

I want to display all record from table. ex. From Student table. And particular student information on the first row. ex. student id = 3 at top then display other student information.

So result look like 
Id  Name    City
3   PQR     xyz
1   LMN     xyz
2   ABC     xyz
4   WXY     xyz

Thanks in advance

Upvotes: 0

Views: 55

Answers (3)

Philip Couling
Philip Couling

Reputation: 14913

It's generally better to fire two queries to do this and put the results together in your application.

However there are a couple of techniques that will work.

The two techniques I'd consider are:

1. Use a Union

SELECT * FROM students WHERE id = 3
UNION ALL
SELECT * FROM students where id <> 3;

Or even

SELECT * FROM students WHERE id = 3 
UNION ALL
SELECT * FROM (SELECT * FROM students where id <> 3 order by id) x;

2. A complex order by statement

SELECT * FROM students order by id = 3 desc, d;

Be aware that this is easier to read, but arguably will have lower performance on large well indexed tables as it the optimizer will be unable to use the index for ordering.

Upvotes: 1

Arth
Arth

Reputation: 13110

I like:

  SELECT * 
    FROM student
ORDER BY id = 3 DESC, /* id = 3 returns 1 (true) or 0 (false) */
         id /* This will put your other students in ASC order of id */

Upvotes: 0

juergen d
juergen d

Reputation: 204854

select * from students
order by id <> 3,
         id

or

select * from students
order by case when id = 3 
              then 1
              else 2
         end,
         id

Upvotes: 1

Related Questions