How to get the LAST data for each group?

[Phil Smith]

I thought this was simple. Apparently Not.
Here is my sample data:
Item ID Version PLM Brand
280 0 blank OJK
281 0 red Bulls
281 1 green BullS
317 0 red Ricter
317 1 blue Ricter
320 0 orange Dependent
320 1 Blue Dependent
320 2 Red Dependent

For each group of ITEM IDs, I want the Version,PLM, and BRAND for the
record with the highest Version #. Like So:

Item ID Version PLM Brand
280 0 blank OJK
281 1 green BullS
317 1 blue Ricter
320 2 Red Dependent

My query:

SELECT [PLMDATA].[Item ID], Last([PLMDATA].Version) AS Version,
Last([PLMDATA].PLM) AS PLM, Last([PLMDATA].Brand) AS Brand
FROM [PLMDATA]
GROUP BY [PLMDATA].[Item ID]
ORDER BY [PLMDATA].[Item ID], Last([PLMDATA].Version);

Gives Me Version "0" for every Item ID.

What am I doing wrong?

 

[Bob Barrows]

I thought this was simple. Apparently Not.
Here is my sample data:
Item ID Version PLM Brand
280 0 blank OJK
281 0 red Bulls
281 1 green BullS
317 0 red Ricter
317 1 blue Ricter
320 0 orange Dependent
320 1 Blue Dependent
320 2 Red Dependent

For each group of ITEM IDs, I want the Version,PLM, and BRAND for the
record with the highest Version #. Like So:

Item ID Version PLM Brand
280 0 blank OJK
281 1 green BullS
317 1 blue Ricter
320 2 Red Dependent

My query:

SELECT [PLMDATA].[Item ID], Last([PLMDATA].Version) AS Version,
Last([PLMDATA].PLM) AS PLM, Last([PLMDATA].Brand) AS Brand
FROM [PLMDATA]
GROUP BY [PLMDATA].[Item ID]
ORDER BY [PLMDATA].[Item ID], Last([PLMDATA].Version);

Gives Me Version "0" for every Item ID.

What am I doing wrong?

Two things:
Not using a subquery
Assuming Last will give you the highest version (you should be using
MAX)

This query gives you the highest version for each item group:

SELECT [Item ID], Max([Version]) AS HighestVersion
FROM [PLMDATA]
GROUP BY [Item ID]

For simplicity and readability, I would recommend saving this as a query
called HighestVersionPerItemGroup

Then create a new query that joins [PLMDATA] to
HighestVersionPerItemGroup:

Select p.[Item ID], HighestVersion,PLM, Brand
FROM [PLMDATA] As p join HighestVersionPerItemGroup As h
ON p.[Item ID]=h.[Item ID] and Version = HighestVersion

 

[vanderghast]

LAST does not mean Latest. In your case, it seems you seek the MAXimum value
for the version, and data associated to it.


There are four methods presented at
http://www.mvps.org/access/queries/qry0020.htm, the first one is probably
the most intuitive and easier to maintain.


LAST means "the last record seen by the database engine, given the execution
plan it comes with". It is useful to get multiple values (from multiple
fields) from the same record:


SELECT LAST(firstName), LAST(lastName) FROM ...


will return a existing person name, while:


SELECT MAX(firstname), MAX(lastname) FROM ...


will return Zoe Zeta, not a real person, with the initial data like:

Zoe Alpha
Joe Zeta



(LAST would return one of the record, so, a real person)




Vanderghast, Access MVP

 

[Phil Smith]

Just a question: I still do not understand why my query failed. IF I
am sorting on version, then isn't the LAST record seen for each group
the record with the highest version? Looking at your examples below,
LAST would give me what I want, while MAX would not. I understand the
method suggested by Bob, but I am wondering what I am missing on my own
query?

Thanx

LAST does not mean Latest. In your case, it seems you seek the MAXimum
value for the version, and data associated to it.


There are four methods presented at
http://www.mvps.org/access/queries/qry0020.htm, the first one is
probably the most intuitive and easier to maintain.


LAST means "the last record seen by the database engine, given the
execution plan it comes with". It is useful to get multiple values (from
multiple fields) from the same record:


SELECT LAST(firstName), LAST(lastName) FROM ...


will return a existing person name, while:


SELECT MAX(firstname), MAX(lastname) FROM ...


will return Zoe Zeta, not a real person, with the initial data like:

Zoe Alpha
Joe Zeta



(LAST would return one of the record, so, a real person)




Vanderghast, Access MVP

I thought this was simple. Apparently Not.
Here is my sample data:
Item ID Version PLM Brand
280 0 blank OJK
281 0 red Bulls
281 1 green BullS
317 0 red Ricter
317 1 blue Ricter
320 0 orange Dependent
320 1 Blue Dependent
320 2 Red Dependent

For each group of ITEM IDs, I want the Version,PLM, and BRAND for the
record with the highest Version #. Like So:

Item ID Version PLM Brand
280 0 blank OJK
281 1 green BullS
317 1 blue Ricter
320 2 Red Dependent

My query:

SELECT [PLMDATA].[Item ID], Last([PLMDATA].Version) AS Version,
Last([PLMDATA].PLM) AS PLM, Last([PLMDATA].Brand) AS Brand
FROM [PLMDATA]
GROUP BY [PLMDATA].[Item ID]
ORDER BY [PLMDATA].[Item ID], Last([PLMDATA].Version);

Gives Me Version "0" for every Item ID.

What am I doing wrong?

 

[Phil Smith]

Just a question: I still do not understand why my query failed. IF I
am sorting on version, then isn't the LAST record seen for each group
the record with the highest version? Looking at your examples below,
LAST would give me what I want, while MAX would not. I understand the
method suggested by Bob, but I am wondering what I am missing on my own
query?

Thanx

LAST does not mean Latest. In your case, it seems you seek the MAXimum
value for the version, and data associated to it.


There are four methods presented at
http://www.mvps.org/access/queries/qry0020.htm, the first one is
probably the most intuitive and easier to maintain.


LAST means "the last record seen by the database engine, given the
execution plan it comes with". It is useful to get multiple values (from
multiple fields) from the same record:


SELECT LAST(firstName), LAST(lastName) FROM ...


will return a existing person name, while:


SELECT MAX(firstname), MAX(lastname) FROM ...


will return Zoe Zeta, not a real person, with the initial data like:

Zoe Alpha
Joe Zeta



(LAST would return one of the record, so, a real person)




Vanderghast, Access MVP

I thought this was simple. Apparently Not.
Here is my sample data:
Item ID Version PLM Brand
280 0 blank OJK
281 0 red Bulls
281 1 green BullS
317 0 red Ricter
317 1 blue Ricter
320 0 orange Dependent
320 1 Blue Dependent
320 2 Red Dependent

For each group of ITEM IDs, I want the Version,PLM, and BRAND for the
record with the highest Version #. Like So:

Item ID Version PLM Brand
280 0 blank OJK
281 1 green BullS
317 1 blue Ricter
320 2 Red Dependent

My query:

SELECT [PLMDATA].[Item ID], Last([PLMDATA].Version) AS Version,
Last([PLMDATA].PLM) AS PLM, Last([PLMDATA].Brand) AS Brand
FROM [PLMDATA]
GROUP BY [PLMDATA].[Item ID]
ORDER BY [PLMDATA].[Item ID], Last([PLMDATA].Version);

Gives Me Version "0" for every Item ID.

What am I doing wrong?

 

[John W. Vinson]

IF I
am sorting on version, then isn't the LAST record seen for each group
the record with the highest version?

That would be nice, but it's not the way Access works. My understanding is
that it will retrieve the last record *in disk storage order*, an order which
you cannot control.

 

[vanderghast]

Also, SORTING may be done AFTER the grouping is done (depends on the
execution plan), and thus, LAST won't be necessary matching the record with
the MAXimum (sorted ASCendingly) value. It is preferable to see LAST as
"coming from a record, more or less randomly selected within the GROUP",
even if it is not really random in the statistical meaning of that word.


Vanderghast, Access MVP

The MAX operator is what you want, but not in the way you tried to use the
LAST operator, which, as Vanderghast explained, does not mean 'last in the
(ascending) sort order'. The MAX operator does mean that, but if you
applied
it to each column in the way you did with the LAST operator you'd get a
mixed
bag of values, the highest for each. To do what you want in one query you
can use a subquery to restrict the results to the rows with the MAX
version
number for each group, like so:

SELECT *
FROM PLMDATA AS P1
WHERE Version =
(SELECT MAX(Version)
FROM PLMDATA AS P2
WHERE P2.[item ID] = P1.[item ID]);

The aliases P1 and P2 differentiate the two instances of the table,
allowing
the subquery to be correlated with the outer query on the item ID columns.
The subquery returns the MAX (highest) version number for each item ID,
thus
restricting the outer query's results to the rows where the item ID is the
highest for each distinct item ID value.

The above query is in principle much the same as the 2-query approach, the
subquery acting in the same way as the query returning the highest version
number per item ID, and its correlation with the outer query being the
equivalent of joining the table to the query in the final query.

Ken Sheridan
Stafford, England

Just a question: I still do not understand why my query failed. IF I
am sorting on version, then isn't the LAST record seen for each group
the record with the highest version? Looking at your examples below,
LAST would give me what I want, while MAX would not. I understand the
method suggested by Bob, but I am wondering what I am missing on my own
query?

Thanx

LAST does not mean Latest. In your case, it seems you seek the MAXimum
value for the version, and data associated to it.

[quoted text clipped - 54 lines]

What am I doing wrong?

 

[vanderghast]

Also note that the execution plan ***may*** come with sorting on the GROUP
fields, to group and aggregate efficently, and only after the aggregates are
computed, should he come with sorting the intermediate result with the ORDER
BY you impose. So, even if you specify an ORDER BY, it is not necessary done
before the aggregation process would be performed.


Vanderghast, Access MVP