Yes, Min([Self].[Fld3]) should be Min([Self].[Fld1]), and the sub-query
determines the lowest sequence number in the group.
Using [Your Table] AS [Self] assigns an "alias" to the table selected from
in the subquery. We need to distinguish between it and the table selected
from in the outer query, since they are in fact the same table.
If it helps, the English equivalent of the query might go something like
this...
Select the values of Fld3 and Fld6 from the records in Your Table where
the
value of Fld1 is equal to the minimum value of Fld1 over all records in
Your
Table having the given value of Fld3.
Another approach that may be easier to follow and still yield the same
results, although with different performance, might be:
1. Create a query (say, named "Query1") that finds the maximum value of
Fld1 for each value of Fld3. The SQL might look something like this:
SELECT
[Your Table].[Fld3],
Max([Your Table].[Fld1]) AS [Fld1]
FROM
[Your Table]
GROUP BY
[Your Table].[Fld3]
2. Create a second query that joins the first query to your table to
select
the records you want. The SQL might look something like this:
SELECT
[Your Table].[Fld3],
[Your Table].[Fld6]
FROM
[Your Table]
INNER JOIN
[Query1]
ON
[Your Table].[Fld3] = [Query1].[Fld3]
AND
[Your Table].[Fld1] = [Query1].[Fld1]
Both approaches assume that the combination of Fld3 and Fld1 is unique.
Peter A said:
Many thanks, Brian.
The first suggestion is a simple solution I don't know why I didn't think of
myself. I was actually looking at this myself on behalf of a friend so
I'm
not sure whether the sequence always begins with 1, but on the face of
it,
it looks like the obvious solution.
If I understand the second suggestion correctly, it has the same
essential
structure as the first one except that the value in the WHERE clause is
itself a sub-query. (The "Min([Self].[Fld3])" should though be
"Min([Self].[Fld1])" I think.) This sub-query simply determines the
lowest
'sequence number' in the group, right? The self-reference nevertheless
has
my head spinning! The sub-query is named AS "myname" and then refers to
itself... Could you explain how this works, please??
Peter
Brian Camire said:
Despite the name, the value returned by First (and Last) is in effect
arbitrarily selected from the values of the aggregated records.
Since you already have a sequence number field, will a query whose SQL
looks
something like this:
SELECT
[Your Table].[Fld3],
[Your Table].[Fld6]
FROM
[Your Table]
WHERE
[Your Table].[Fld1] = 1
ORDER BY
[Your Table].[Fld3]
give you what you want?
If not (for example, because Fld1 doesn't necessarily start from 1),
you
might try a query whose SQL looks something like this:
SELECT
[Your Table].[Fld3],
[Your Table].[Fld6]
FROM
[Your Table]
WHERE
[Your Table].[Fld1] =
(SELECT
Min([Self].[Fld3])
FROM
[Your Table] AS [Self]
WHERE
[Self].[Fld3] = [Your Table].[Fld3])
ORDER BY
[Your Table].[Fld3]
I have a Table like the following:
Fld1 Fld2 Fld3 Fld4 Fld5 Fld6
1 34 4 63 101 P03
4 42 4 72 102 P12
2 36 4 92 224 P35
1 25 6 86 192 P26
2 27 6 54 282 P43
And a simple query of the form:
SELECT Fld3, Fld1, Fld6 FROM Table1 ORDER BY Fld3, Fld1;
Fld1 can be regarded as a sequence number within the groups of records
having the same value of Fld3.
Now I want a further query that lists just the first record in each group
within the foregoing query (Query1), as follows:
SELECT Fld3, First(Fld6) AS FirstFld6 FROM Query1
GROUP BY Fld3 ORDER BY Fld3;
The resulting value of Fld6 is however not always that corresponding
to
the
first record in the group as ordered by Query1.
Should this work or not, can someone tell me?
Thanks
