Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
Sub select query...version 4.016
I revisited, and you are absolutely correct...when I remove maxdate from both the select and order clauses...the answer is the same. Thanks again. drdickson -
Sub select query...version 4.016
Rudy...happily I will comply...what I get is an error: [MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt]Unknown column 'maxdate' in 'order clause' Because there is not definition of 'maxdate'. What I need to show is how many entries I made on December 1 (as an example of the latest date I added... -
Sub select query...version 4.016
Thanks to all for your input...you definitely put me on the path to discovery...here is the final answer... 1> select fd_Date ,sum(1) as DateCount, MAX(fd_date) as maxdate from Products Where qty_in_stock > 0 group by fd_date order by maxdate desc limit 1 Again, thanks to all... -
Sub select query...version 4.016
Which indeed is exactly correct...in terms of what the answer needs to be...however in your: select fd_date, sum(1) as datecount from products group by fd_date order by maxdate desc limit 1; There is no value for "maxdate" and so, an error is returned...which has always been my... -
Sub select query...version 4.016
Rudy: Thanks for your response, and your query did indeed return a value...unfortunately, though it limits the return to one value...it is the accumulative COUNT of all of the fd_dates...that's the reason the ORIGINAL query (that works in MSSQL) SELECT COUNT(fd_Date) as DateCount, MAX(fd_date)... -
Sub select query...version 4.016
Thanks...and yes I've looked at that manual entry...however, for the query I'm writing, it is not particularly helpful...as you can tell, I'm selecting a DATE count and from that, displaying only the latest date. The manual entry for the IN(select...assumes two tables (I'm using but one) and... -
Sub select query...version 4.016
I'm trying to get the MSSQL acceptable syntax: SELECT COUNT(fd_Date) as DateCount, MAX(fd_date) as maxdate from Products WHERE Products.fd_Date IN(select MAX(fd_Date) AS maxdate FROM Products) to work. It was my understanding that subselects were supported in the production version of MySQL...
