3

I am making a program in php to select records from database in alphabetical order by adding, "order by 'column name'" in my query string

for example we have entries

A B M L H V V F now after showing records in alphabetical order it will fetch records in following order

A B F H L M V V

now if a my user want to see records of m then he should get records in following manner

M A B F H L V V

means searched record at top and then in alphabetical order.

So how can I get records in this manner by one query

$result=mysqli_query($con,"SELECT * from orders order by 'name'");
Ghanshyamji Gupta
  • 429
  • 4
  • 16

3 Answers3

1

Use order by name='M' desc

SELECT * from orders order by name='M' desc,name ASC
Abhishek Sharma
  • 6,689
  • 1
  • 14
  • 20
1

You can try this

select * 
from orders
order by name = 'M' desc, name asc

Or

select * 
from orders
order by case when name = 'M' then -1 else name end

Source: mysql SQL: specific item to be first and then to sort the rest of the items

Community
  • 1
  • 1
Suyog
  • 2,472
  • 1
  • 14
  • 27
  • Unless MySQL will automatically cast that -1 to a string, that `case` won't work. If that's true then it relies on the hyphen character sorting before all the real names. – shawnt00 Nov 26 '15 at 13:37
0

Use two queries, the first grabbing all records for the the required letter, using the query in the question as a starting point (assuming required letter is M):

SELECT * FROM orders WHERE name LIKE '%m' ORDER BY 'name'

then for the second query:

SELECT * FROM orders WHERE name NOT LIKE '%m' ORDER BY 'name'

Then have PHP append the result set from the second query to the result set of the first. The field that is used in the ORDER BY clause will need to match in both queries. I don't think it's possible to do if you want both parts sorted in order in one query

SpacePhoenix
  • 607
  • 1
  • 5
  • 15