I have written this query to get the last 20 records of a commodity:
SELECT TOP 20 CurrentMonthData.[Symbol], CurrentMonthData.[DeliveryMonth], CurrentMonthData.[DDate], CurrentMonthData.[Close]
FROM CurrentMonthData
WHERE (((CurrentMonthData.[Symbol])="AD") AND ((CurrentMonthData.[DeliveryMonth])=#12/1/2007#))
ORDER BY CurrentMonthData.[DDate] DESC;
But I need to compare today's close to the last 20 records from yesterday. Is there a way to say the Top 20 records from Yesterday? Then I can compare today's close to the maximum of the last 20 day's close. If anybody can help with this query I would greatly appreciate it.
SELECT TOP 20 CurrentMonthData.[Symbol], CurrentMonthData.[DeliveryMonth],
CurrentMonthData.[DDate], CurrentMonthData.[Close]
FROM CurrentMonthData
WHERE (((CurrentMonthData.[Symbol])="AD") AND
((CurrentMonthData.[DeliveryMonth])=#TheDateThatIndicatesYESTERDAY#))
ORDER BY CurrentMonthData.[DDate] DESC;
Although with this code Tyler, you are ordering the records by DDate, which is not necessary when you're just wanting the TOP 20 AND the criteria contains an "=" sign (meaning you are looking for a specific value) for the same field on which you are ordering. The ORDER BY in this case is redundant data, although it is still evaluated (but has no effect on the data's output).
To answer your general question (from both your posts) about grouping, I think you're pretty much going to have to create two queries to get done what you need to. I say this because of your situation. To get it done in one query, you would need to....
* GROUP BY 3 fields to get unique records
* Perform a second GROUPING with a TOP predicate
* Use at least one subquery
* Use an aggregate function (MAX, or possibly LAST or TOP 1) in the subquery (which would require a separate grouping of all table fields on it's own).
Just reading the above, I don't think one query will do it for you.
Thank you for your advise. Can you explain to me how using the three fields together to get a primary key (unique Value) is important? Is that how you can separate contract information so that a query will look at the Contract of Corn Expiring in December of 2007 separate from the Aussie Dollar Contract Expiring in March of 2008? I know that this is a key part of my problem as the query I have written doesn't evaluate the contracts separately. Can you help explain/straighten me out on this? Thanks for your help
If "unique value" means "unique record (combination of all fields in the table)", then using the 3 fields together in a GROUP BY, or even as a composite key is irrelevant. Using the fields together as a composite is just another way of assigning uniqueness to the records. In your case, it is CLOSE to the same thing as assigning an autonumber to each record, but I wouldn't say the two necessary serve the same purpose here.
Is that how you can separate contract information so that a query will look at the Contract of Corn Expiring in December of 2007 separate from the Aussie Dollar Contract Expiring in March of 2008?
Not necessarily. Actually, I didn't fully understand this question. I might have to see a database to answer it with any certainty. But, let me give you some facts that might help...
Both of the following SQL statements will return exactly the same recordset...
Code:
SELECT symbol, month, date, close
FROM table
GROUP BY symbol, month, date, close
Code:
SELECT DISTINCT symbol, month, date, close
FROM table;
SELECT TOP 20 CurrentMonthData.[Symbol], CurrentMonthData.[DeliveryMonth], CurrentMonthData.[DDate], CurrentMonthData.[Close]
FROM CurrentMonthData
WHERE (((CurrentMonthData.[Symbol])="AD") AND ((CurrentMonthData.[DeliveryMonth])=#12/1/2007#))
ORDER BY CurrentMonthData.[DDate] DESC;
....it is not evaluating correctly because you are probably writing the incorrect statement for what you really want. A couple of more facts for you (sorry for the overload of information )....
** TOP is most commonly used (at least by me) to query aggregate extremes (Min, Max). Max(expression) can get you one record, whereas "TOP 20" with "ORDER BY DESC" can get you 20 Max's (from best to worse / most to least / biggest to smallest).
** Predicates, (TOP, LAST, etc...) in general, only specify the LOCATION in the table from which to start pulling records. Thus, if a table is not sorted, TOP 20 will return exactly that, just as it appears entered in your source table. However, if there is an ORDER BY or GROUP BY clause present, TOP 20 will return 20 records that have been subject to the appropriate clause in the statement.
** So, in all reality, a SELECT predicate, when combined with query organization (a clause), is evaluated LAST.
Sorry for the novel, but here is my "comprehension recipe" when answering questions.... lots of complexity = lots of explanation
