simple ORDER by thing - can't get my head round it

steamPunk

I have a table 'actu_mail' that contains a list of users that have signed-up for a newsletter.

they also have a region id 'reg_id' so we know roughly where they are

I want to do a simple "SELECT * FROM 'actu_mail' ORDER BY 'reg_id' " but i would like to order it so that the the 'reg_id' that has most occurrences in the table comes out first and the 'reg_id' that has least occurrences in the table comes out last in the order

is it possible to do this with 1 query ?

if not it doesn't matter because i can get round it but it would be great to do it all in 1 go

thanks for any ideas you may have

wilku

You can join the subquery result to your table

SELECT * 
FROM 'actu_mail'
LEFT JOIN 
    (SELECT reg_id,COUNT(reg_id) AS cnt FROM 'actu_mail' GROUP BY reg_id ) AS cnt_table
USING reg_id
ORDER BY cnt_table.cnt

I'm not an SQL expert, so it's just a proposition.

chasiv

SELECT *, COUNT(*) AS times FROM 'actu_mail' GROUP BY 'reg_id' ORDER BY times DESC

This should do it for you...

steamPunk

thanks your for your replies - where I'm stuck is how to get the number of records for each reg_id

my table is this :

CREATE TABLE `actu_mail` (
  `actum_id` int(11) NOT NULL auto_increment,
  `actum_prenom` tinytext NOT NULL,
  `actum_nom` tinytext NOT NULL,
  `actum_email` tinytext NOT NULL,
  `reg_id` smallint(6) NOT NULL default '0',
  `func_id` smallint(6) NOT NULL default '0',
  `actum_org` tinytext NOT NULL,
  `actum_addr` tinytext NOT NULL,
  `actum_abb` tinyint(4) NOT NULL default '0',
  `actum_exported` tinyint(4) NOT NULL default '0',
  KEY `actum_id` (`actum_id`)
)

and the query code that i tried it with is this :

[code=php]$query = "SELECT *, COUNT(*) AS times FROM actu_mail GROUP BY reg_id DESC";
$res2 = mysql_query($query);
while($row2 = mysql_fetch_array($res2)){
	$reg_id = $row2['reg_id'];
	$regArray[$reg_id]["count"]++;
	echo "<br />".$reg_id;
}[/code]

this outputs a descending list of reg_id but it's not in the order of the reg_id with most records gong down to the reg_id with least records - also i don't know how to get the number of records for each reg_id

chasiv

you changed my recommended SQL. times is the field that holds the # of reg_id records in my SQL. You skipped ORDER BY times.

steamPunk

oops - that must have been while i was fiddling around
my apologies
thanks 🙂

chasiv

no problem... but that should make it work for you... you can then reference each total with $row2['times'].