Sub select query...version 4.016

[drdickson]

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 4.016...this is not correct evidently as the above syntax fails. How would one re-write the above to work in MySQL? Many thanks in advance...

drdickson

 

[sleipnir214]

Nope. You have to run 4.1+ to get subqueries.

A subquery like the one you're trying can be rewritten to work with older versions of MySQL. See

http://www.mysql.com/doc/en/Rewriting_subqueries.html

Want the best answers? Ask the best questions:

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

TANSTAAFL!!

 

[drdickson]

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 does not address the Max(field)syntax or how I would go about either grouping or joining those values to have ONLY the latest date displayed. Many thanks regardless for your reply.

drdickson

 

[r937]

drdickson, the subquery can only return one value, right?

so why use IN instead of equal?

and the WHERE clause will select only those dates that are equal to the max date, right?

so why select max in the outer query? to avoid grouping?

i just felt your query was weird -- it may work, but it's weird

anyhow, here's an alternative that may not be terribly efficient but at least gets the same answer --

[tt]select fd_Date
, count(*) as DateCount
from Products
group
by fd_date
order
by fd_date desc
limit 1 [/tt]


rudy

http://r937.com/

 

[drdickson]

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) as maxdate from Products
WHERE Products.fd_Date IN(select MAX(fd_Date) AS maxdate FROM Products)

selects the count AND the maxdate and then refines that by counting ONLY those values in the MAX date. Thanks again...

drdickson

 

[sleipnir214]

As I understand the query, the question it's designed to ask is, "What is the maximum value of fd_date from the products table, and how many records fd_date value match that value?"

Given the following table:

+------------+
| fd_date    |
+------------+
| 2003-01-01 |
| 2003-05-05 |
| 2003-04-04 |
| 2003-05-05 |
| 2003-02-02 |
| 2003-03-06 |
+------------+

Then the query:

select
fd_date,
sum(1) as datecount
from products
group by fd_date
order by maxdate desc
limit 1;

(A small variation from the query posted by r937)

Returns the following:

+------------+-----------+
| fd_date    | datecount |
+------------+-----------+
| 2003-05-05 |         2 |
+------------+-----------+

Which sounds to me exactly what you want.

Want the best answers? Ask the best questions:

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

TANSTAAFL!!

 

[drdickson]

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 problem...as maxdate is not seen as a valid value in a "GROUP" or "WHERE" clause. The above causes an;

ODBC Error Code = S0022 (Column not found)

[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-nt]Unknown column 'maxdate' in 'order clause'

SQL = "select fd_date, sum(1) as datecount from products group by fd_date order by maxdate desc limit 1;"

error, as maxdate is not a defined variable. AND if defined, maxdate will not be recognized as a valid GROUP or WHERE function. So...though you certainly understand the expected value...we still do not have resolution...but many thanks for your attempt...

drdickson

 

[drdickson]

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 who responded and helped me get my head around this issue...

Cheers,

drdickson

 

[r937]

drdickson, your last query is still too weird for me

why would you group by fd_date AND select max(fd_date) for each such group -- they MUST be the same value for each fd_date

try this: remove the MAX(fd_date) as maxdate, order instead by fd_date desc, and tell me what you get


rudy

 

[drdickson]

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 items). On the first of each month since July I have been adding new product at the beginning of each month. What I want to display is HOW MANY of an item I have added since the last date. IF I sort ONLY on fd_date, WITHOUT a maxdate, what I get is the TOTAL of ALL of the items I have added since July (if I don't include the maxdate in the sort order)...not those I added on the first of December. So in one case I get 6588 items, and in the other I get 1304...it is the latter that I am interested in. Hope this is clear.

drdickson

 

[drdickson]

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

 

[r937]

you got the error because you didn't change the ORDER BY as i suggested

try this:

[tt]select fd_Date
, sum(1) as DateCount
from Products
Where qty_in_stock > 0
group
by fd_date
order
by fd_date desc
limit 1[/tt]

 

[r937]

okay, glad you got it sorted

now if you go way back up to my first reply and compare the sql, there's a slight difference, it said COUNT(*) instead of SUM(1), which i believe you will find to be equivalent, although COUNT(*) is more efficient (probably only slightly more, but nevertheless...)

;-)

 

[sleipnir214]

Yeah, that's true. Count(*) and sum(1) are functionally identical, but count(*) if performed from the table descriptor without actually counting records.



Want the best answers? Ask the best questions:

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

TANSTAAFL!!