0

There is some problem in sorting with mysql to show records in ASC or DESC order.

there is field in mysql id, name, status

my result with mysql query with sort order by id ASC

 - 1, DE10, 1
 - 2, DE15, 1
 - 3, DE20, 1
 - 4, 4DE40, 1
 - 5, 5DE70, 1
 - 6, DE100, 1
 - 7, DE35, 1

by want to display result as below mention format

 - 1, DE10, 1
 - 2, DE15, 1
 - 3, DE20, 1
 - 7, DE35, 1
 - 4, 4DE40, 1
 - 5, 5DE70, 1
 - 6, DE100, 1

how is that possible in mysql or any other solution for the same

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Lalit Parkash
  • 42
  • 2
  • There is no order to your intended format. I think you will need to do this with a custom function after you get the result set – Barry Thomas Jan 25 '17 at 11:00
  • hey, barry thanks for your answer, there is no any other way to sort the result so that this 7, DE35, 1 will come in asc order as like numeric we can sort – Lalit Parkash Jan 25 '17 at 11:04
  • I dont think so, if you sort by name ASC the 4, 4DE40, 1 and 5, 5DE70, 1 will be at the top. I am not an expert, but from my experience you can't get this from basic mysql sorting – Barry Thomas Jan 25 '17 at 11:07
  • yeah sorry this is by mistake typed 4, 4DE40, 1 -- 5, 5DE70, 1 the actual values are 4, DE40, 1 -- 5, DE70, 1 – Lalit Parkash Jan 25 '17 at 11:12
  • actually i want to show records with respect to numeric asc as in name like 10, 15, 20, 35, 40, 70, 100 – Lalit Parkash Jan 25 '17 at 11:13
  • 1
    Ah yes, I see now. AFAIK this is possible in mysql, but it will be quiet complex. would it be easier for you to store the numeric part in a seperate column and sort by that? like (1, DE10, 1, 10) (1, DE15, 1, 15)(1, DE20, 1, 20) sort by the last column – Barry Thomas Jan 25 '17 at 11:19
  • 1
    @BarryThomas Can you show us the quite complex method anyway? ;-) – Strawberry Jan 25 '17 at 11:23
  • I honestly don't know myself, I imagine you would need to split the value of the name column and then treat the first part as a string and the second as an int. I did mention above I am not an expert. Also, I did not intend for my comment above to imply I knew how. Apologies if it did. – Barry Thomas Jan 25 '17 at 11:25
  • @Strawberry This may be a way. http://snipplr.com/view/41275/ from this answer http://stackoverflow.com/questions/8557172/mysql-order-by-sorting-alphanumeric-correctly – Barry Thomas Jan 25 '17 at 11:30

1 Answers1

-1

If I understand, you might sort ID column by ASC or DESC order ?

You could apply a MySQL query like that :

#Sort by ASC order :
SELECT id, name, status from "your_table" ORDER BY id ASC ;

#Sort by DESC order :
SELECT id, name, status from "your_table" ORDER BY id DESC ;
Essex
  • 6,042
  • 11
  • 67
  • 139