Thread: Using count(*) in where question

Code: SELECT t.title , p.last_post FROM Topic AS t INNER JOIN ( SELECT idtopic , MAX(dateadded) AS last_post FROM Post GROUP BY idtopic ) AS p ON p.idtopic = t.idtopic WHERE t.idforum =1 ORDER BY p.last_post DESC

btw, someone please shoot me in the other foot

Quote: Originally Posted by JarlH You originally claimed I said that you can have a column in the GROUP BY that is not in the SELECT list. Here you are trying to do the opposite, a column id in the SELECT list which " is not contained in either an aggregate function or the GROUP BY clause ". Sorry for that, that was indeed incorrectly put on my side. My example statement is valid for MySQL and that's what I wanted to...

Maybe we will have to run the SQL directly indeed. But then we do have the problem that the SQL isn't working with MySql and we have to make sure that we are independend of the dbms used. So if we decide to run it directly we need another SQL nevertheless - one for all dbms. Some help? ;-)

Quote: Originally Posted by shammat Sorry for that, that was indeed incorrectly put on my side. My example statement is valid for MySQL and that's what I wanted to point out (the shortcomings of grouping in MySQL) Re-reading my posts from a "distance" I do understand the confusion. Sorry again, next time I'll try to be more precise... Never mind. Have a nice weekend!

You can also use "COALESCE(NULLIF(actual, 0), estimated)".

Quote: Originally Posted by Pat Phelan What if there are ties (such as seventy-two majors that only have one student)? What if the majors with the fewest advisors are not the majors with the most students? -PatP Thank you PatP. and yes that's the problem that I am facing now. the majors i got form the database is not the most student. sign~ I sent a email to my TA and my teacher already last night already but haven't get any reply...

i think i got it, thank you for explaining

What DBMS are you using? ANSI update behaviour/syntaxt differs from, say, Microsoft SQL Server, so yo may get a more appropriate answer if you supply us with this information

I got it. I used a case statement.