Aggregate Function and DISTINCT

[MarcHav]

Below code gives me the correct data BUT I would like to display other fields from the table, now it just displays two rows of data. How can I display more ?


$query5="select count(*) AS TOTAL from (select CName,Max([Date/Time]) AS [Date_Time] from $table
where [MS03-013]='NoPatch'
group by CName)";


$querya[5]= "select Distinct CName AS tt,Max([Date/Time]) AS [Date_Time] from $table
where [MS03-013]='NoPatch'
group by CName";

 

[sleipnir214]

Frankly, I'm suprised $query5 works at all. The last time I checked, MySQL does not support subqueries.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[swampBoogie]

It looks like you are in the wrong forum as the syntax with [] is non Mysql. Looks like access/sql server.

 

[MarcHav]

Thanks for the answers.
First of all I c/p too much, no subqueries, just the $querya[5] is what I am referring to.
Secondly, yes, it is MySQL
And last but not least, I just solved it this morning:

$querya[5]="SELECT * from $table as T1
where [Date/Time] in (select MAX([Date/Time]) from $table
where CName=t1.CName group by CName)
AND [MS03_013]='NoPatch'";

Thanks to Sleipnir214 and swampBoogie for answering my question.