Group By Before Order By DESC

[fry2010]

Been a while since iv been on here... Any help appreciated:

 

What I am trying to achieve is to select a specific row from a table, then get the rest of the rows. I have found the solution to this using:

 

GROUP BY (id = 1);

 

However I still want to filter the rest of the rows with the ORDER BY clause.

 

When I try to do this, it does not return the GROUP BY id first.

 

cheers.

[fenway]

You don't use GROUP BY to select a specific row -- that's what WHERE clause is for.

[fry2010]

The reason I am using GROUP BY is because I still want to return all other rows, but select one specific row to display first.

 

the table design is (simplified):

 

create table person (

  id int unsigned not null AUTO_INCREMENT

  name varchar(40) not null,

  PRIMARY KEY (id)

);

 

create table order (

  id int unsigned not null AUTO_INCREMENT,

  personid int unsigned not null,

  productid int unsigned not null,

  name varchar(40) not null,

  PRIMARY KEY (id),

  FOREIGN KEY (personid) references person(id),

  FOREIGN KEY (productid) references product(id)

);

 

create table product (

  id int unsigned not null,

  name varchar(40) not null,

  PRIMARY KEY (id)

);

 

 

query:

 

select a.*, b.*, c.*

FROM person AS a

LEFT JOIN order AS b ON a.id = b.personid

LEFT JOIN product AS c ON c.id = b.productid

WHERE

c.name = 'some product'

GROUP BY (b.id = 43) DESC

ORDER BY b.name;

 

Please note the tables and query have been condensed a lot, just to give the idea of what im trying to achieve.

 

So I am basically wanting to get the data for orders that have a certain product name, in name order, but I also want to select a specific order id, even if that has the product name in it or not. I want that row to be the first returned row.

 

I appreciate your guidence.

 

 

[fenway]

You can use the same expression in your ORDER BY clause, and that row will sort first.

[fry2010]

Fenway, your a legend! I didnt even think to try that.

 

cheers.