How do I query multiple data values in same field?

[dcMcK]

I need to create a query that returns first record of every group. So far, I
can write sql statement to pull all occurance of defined groups (i.e. Peach,
Apple). But can't pull first record of any group. I'm new to query! How do I
accomplish this?

Apple (500-record occurances)
Orange (400-records occurances)
Peach (50-record occurances)
banana (20-record occurances)

Results: first record of each group:\apple\orange\peach\banana

 

[Marshall Barton]

I need to create a query that returns first record of every group. So far, I
can write sql statement to pull all occurance of defined groups (i.e. Peach,
Apple). But can't pull first record of any group. I'm new to query! How do I
accomplish this?

Apple (500-record occurances)
Orange (400-records occurances)
Peach (50-record occurances)
banana (20-record occurances)

Results: first record of each group:\apple\orange\peach\banana

There is no such thing as a "first" record. Each record
would have to have one or more fields that can be used to
sort the records in a unique way before you can define what
"first" means.

Without that, you may get the records in any order and which
one appears first is close to a random chance. If you don't
care which one you get, then you can use something like:

SELECT [group], First(thingy)
FROM table
GROUP BY [group]

 

[KenSheridan via AccessMonster.com]

If you want to return more than the column by which the query is grouped and
the column whose value determines which is 'first' per group, and 'first' is
not an arbitrary selection of one row from each group, then it gets a little
more complex. Its perhaps easier to explain this with a more realistic
example:

Say you have a table Orders with columns CustomerID, OrderDate, Amount and
you want the first order, i.e. the earliest, per customer then you cannot
simply GROUP BY CustomerID and return the MIN of OrderDate and OrderAmount.
This would return the earliest date per customer, but the smallest amount,
which could be the amount for any order, not necessarily the earliest one.

The way its done is to use a subquery to find the earliest OrderDate per
CustomerID and then return the rows with that date per customer in the query,
like this:

SELECT CustomerID, OrderDate, Amount
FROM Orders AS O1
WHERE OrderDate =
(SELECT MIN(OrderDate)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID);

Note how the two instances of the Orders table are differentiated her by
giving them aliases O1 and O2. This enables the subquery to be correlated
with the (outer) query on the CustomerID column. By including the subquery
in the query's WHERE clause it restricts the query's results to those rows
where for each customer the OrderDate is the date returned by the subquery
for the customer in question. The subquery uses the MIN operator to return
the earliest date, so its only the orders with the earliest date per customer
which are returned.

Ken Sheridan
Stafford, England