CODEWITHSUNDEEP
mysqlsql
Daniel
Daniel

Reputation: 53

MySQL put a specific row at the top of the result

I'm doing a basic SQL select query which returns a set of results. I want a specific row which the entry "Fee" to be put at the top of the results, then the rest.

Something like: 

SELECT * FROM tbl ORDER By Charges = Fee DESC, Charges DESC

Can anyone help?

Upvotes: 2

Views: 1007

Answers (4)

user12755832
user12755832

Reputation: 1

SELECT * FROM tbl ORDER By FIELD(Charges, 'Fee') DESC

You can use something like the above. Where Charges is the field and fee the specific value. That way you can keep it simple.

Upvotes: 0

MrMarlow
MrMarlow

Reputation: 856

You would have to use ORDER BY with a FIELD attribute, which would then order by those first.

As I don't have your table definitions, I have throw one together here http://sqlfiddle.com/#!9/91376/13

For sake of it disappearing, the script pretty much consists of;

CREATE TABLE IF NOT EXISTS `tbl` (
  `id` int(6) unsigned AUTO_INCREMENT,
  `Name` char(6) not null,
  `Charges` char(10) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO `tbl` (`Name`, `Charges`)
     VALUES ('One', 'Fee'), ('Two', 'Charge'), ('Three', 'Charge'), 
            ('Four', 'Charge'), ('Five', 'Fee'), ('Six', 'Fee'), 
            ('Seven', 'Invoice'), ('Eight', 'Fee'), ('Nine', 'Invoice'), 
            ('Ten', 'Invoice');

    SELECT *
      FROM tbl
  ORDER BY FIELD(`Charges`, 'Charge') DESC
           ;

Which returns:

id  Name    Charges
2   Two Charge
3   Three   Charge
4   Four    Charge
1   One Fee
9   Nine    Invoice
8   Eight   Fee
7   Seven   Invoice
6   Six Fee
5   Five    Fee
10  Ten Invoice

So, to directly answer your question, your query would be;

  SELECT * 
    FROM tbl
ORDER BY FIELD(Charges, 'Fee') DESC

edit : Viewable, sorted by Charges = Fee here : http://sqlfiddle.com/#!9/91376/15

Upvotes: 0

geof2832
geof2832

Reputation: 136

You could try this :

SELECT * from tbl ORDER BY CASE WHEN Charges = 'Fee' THEN 0 ELSE 1 END, Charges DESC;

Upvotes: 2

ADyson
ADyson

Reputation: 62008

I think you'd have a use a UNION query. ORDER BY doesn't support this kind of thing by default as far as I know.

Something like this:

SELECT * FROM tbl WHERE Charges = 'Fee'
UNION
SELECT * FROM tbl ORDER BY Charges DESC

Upvotes: 0

Related Questions