[RESOLVED] GROUP BY ORDER BY problem

steamPunk

I have a table containing products that i would like to list in alphabetical order (with 'pr_nom') but grouped by category ('fam_id')

the table looks like this :

pr_id smallint(6) NOT NULL auto_increment,
pr_nom tinytext NOT NULL,
pr_prix decimal(4,2) NOT NULL default '0.00',
fam_id smallint(6) NOT NULL default '0',
subfam_id smallint(6) NOT NULL default '0',
pr_desc tinytext NOT NULL,
pr_orig tinytext NOT NULL,
pr_ref tinytext NOT NULL,
unite_id tinyint(4) NOT NULL default '0',
pr_vol varchar(10) NOT NULL default '',
pr_coeur tinytext NOT NULL,
pr_image tinytext NOT NULL,
pr_online tinyint(4) NOT NULL default '0',
KEY pr_id (pr_id,fam_id,subfam_id),
KEY unite_id (unite_id)

if i do

$sql = "SELECT * FROM produits ORDER BY pr_nom";

it works fine and returns a list of products in alpha order, but if i do

$sql = "SELECT * FROM produits GROUP BY fam_id ORDER BY pr_nom";

it just returns 6 results because I have 6 different fam_ids in the table

what do i need to do ?

thanks

Lars_Berg

Give some sample data and the expected result.

steamPunk

when you say sample data - what exactly do i need to post ? and in what format ?

Lars_Berg

Like in this post

http://www.phpbuilder.com/board/showthread.php?t=10335857&highlight=sample+data

and for the result

    fam_id: 1

      kurt
      surt

fam_id: 2

      abel
      babel

or wahtever you do expect.

nemonoman

I think you may mean to have this as a full list of products, ordered first by family id, then alphabetically.

This is a sort and a subsort, not a group by.

SELECT * FROM produits ORDER BY fam_id, pr_nom

steamPunk

aha! a subsort - that was what i needed
i was barking up the wrong tree thinking i needed to use GROUP BY - i didn't know that subsorts were possible

thanks !