quote:

---

Originally posted by d00b13630

Is it possible to write a select statement that will order results descending by most matches of a column value?

eg. i have a table with an object.. including an id number.. a second table with comments on the object.. holds the id number as well. i want to return the object with the most comment rows first.

---

SELECT *
FROM table1 t1
INNER JOIN (SELECT id,COUNT(comments) as commentcount
            FROM table2
            GROUP BY id)t2
ON t1.id=t2.id
ORDER BY t2.commentcount DESC

quote:

---

Originally posted by d00b13630

also, i need to know of it is possible to insert into two tables at the same time..

eg. current insert puts the object into a table with an id... i need to create a row in a second table with the objects id and a value set to 0 by default.. so i tried..

if($action == 'submit_pic'){
$insert_pic = "INSERT INTO pics, ratings ( approved, pic_date, picpath, picheader, picsubheader, submittedby, numcomments, rating) VALUES (1, CURRENT_TIMESTAMP, '".$picpath."', '".$picheader."', '".$picsubheader."', '".$submittedby."', 0, 0)";

if (!mysql_query($insert_pic))
{
die('Error: ' . mysql_error());
}


and i get..

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' ratings ( approved, pic_date, picpath, picheader, picsubheader, submittedby, nu' at line 1

---

quote:

---

Originally posted by visakh16
You cant insert simultaneously onto two tables. You need two seperate INSERT statements. And if your attempt was to get a returned value from first insert and to put it in second use SCOPE_IDENTITY

---

quote:

---

Originally posted by d00b13630

thanks for the quick response man.. i've kind of a noob so please bare with me :) i'm not familiar with the inner join clause.. or sql server. from what you gave me i ended up with this.. but i'm not returning anything :/

"SELECT *, date_format(pic_date, '%c/%e/%Y at %l:%i %p PST') as picdate
FROM pics t1
INNER JOIN (SELECT picid,COUNT(comment) as commentcount
FROM comments
GROUP BY pics_picid)t2
ON t1.picid=t2.pics_picidORDER BY t2.commentcount DESC $limit"

---

quote:

---

Originally posted by d00b13630

quote:

---

Originally posted by visakh16
You cant insert simultaneously onto two tables. You need two seperate INSERT statements. And if your attempt was to get a returned value from first insert and to put it in second use SCOPE_IDENTITY

---

No.. I'm trying to set a default value in a secondary table upon creation of a new row in the primary table.

I've tried a seperate insert statement as well.. like so..

if($action == 'submit_pic'){
$insert_pic = "INSERT INTO pics ( approved, pic_date, picpath, picheader, picsubheader, submittedby, numcomments) VALUES (1, CURRENT_TIMESTAMP, '".$picpath."', '".$picheader."', '".$picsubheader."', '".$submittedby."', 0)";

$insert_rating = "INSERT INTO ratings ( rating) VALUES (0)";

if (!mysql_query($insert_pic&&$insert_rating))
{
die('Error: ' . mysql_error());
}

But it's returning with..
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1

---