2

Say I have a dropdown buttons of credit cards.

My ordering is:

SELECT * from cards;

DISCOVER
MASTER-CARD
VISA

I actually want

MASTER-CARD
VISA
DISCOVER

This is a custom order decided by business.

So I though of maintaining a sequence_id in Mysql

DISCOVER 3
MASTER-CARD 1
VISA 2

So I can get these fields in order of their sequence_id.

SELECT * from cards order by sequence_id;

MASTER-CARD 1
VISA 2
DISCOVER 3

But the problem is when I have to add another card in the table, I will have to custom update all ids. Is there a better way to maintain sequence?

How to define a custom ORDER BY order in mySQL does not solve my problem as I can't specify all the fields in my query as the table is huge and keeps changing.

druk
  • 553
  • 6
  • 16
  • 1
    leave gaps use 10,20,30 etc new one can squeeze in between. This is why old line numbered programming languages always had line numbers in this fashion – e4c5 Aug 12 '16 at 08:30
  • Thanks I think this solves my problem. I can leave a gap of 50 or 100 for a lot of edits. – druk Aug 12 '16 at 08:34

2 Answers2

2

The term sequence_id suggests that you want to use the primary key to keep order information. I see no benefit and lots of hassles in that approach. But it also suggest you don't have a primary key yet, which is not good either.

Just add a dedicated column:

CREATE TABLE cards (
   id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   card_name varchar(50) NOT NULL,
   sort_order INT(10) UNSIGNED NOT NULL DEFAULT 9999999,

   PRIMARY KEY (id)
);

... and change your query to:

SELECT *
FROM cards
ORDER BY sort_order;

The 9999999 default is just a trick to ensure newly added cars show up last until you care sorting them—it's entirely optional.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
1
ORDER BY FIND_IN_SET(card, "MASTER-CARD,VISA,DISCOVER")

will sort with card = 'MASTER-CARD' first, etc.

Rick James
  • 135,179
  • 13
  • 127
  • 222