last row per account

[Knox]

I have a table with an stock number (STOCK) and a sequence number (SEQNO)
and a bunch of other fields (TITLE is one). This table is maintained by a
proprietary software package. I just read it. The sequence number is
unique, but the stock is repeated multiple times. In those cases where the
account appears multiple times, only the last row (as determined by sequence
number) is the good row.

When I want to access this row, I do this cumbersome query which groups by
stock and finds the maximum sequence for each stock number. Then I have
another query which links to the first query by sequence number. I then can
access the correct row.

Is there an easier way using the last function with grouping? In other
words, could I just do a group by stock, last on all the other fields, order
by sequence and would that give me what I want? I've stayed away from last
because I read somewhere it was not deterministic.

For example:

SELECT INVPLUS.STOCK, Last(INVPLUS.TITLE) AS LastOfTITLE
FROM INVPLUS
GROUP BY INVPLUS.STOCK
ORDER BY Max(INVPLUS.SEQNO);

That certains gives me the correct stock number but will the title be
correct?

Thanks in advance,

Knox

 

[John Vinson]

When I want to access this row, I do this cumbersome query which groups by
stock and finds the maximum sequence for each stock number. Then I have
another query which links to the first query by sequence number. I then can
access the correct row.

Is there an easier way using the last function with grouping? In other
words, could I just do a group by stock, last on all the other fields, order
by sequence and would that give me what I want? I've stayed away from last
because I read somewhere it was not deterministic.

Last() returns the last record *in disk storage order* - which is
pretty much uncontrollable. What I'd suggest is a Subquery instead:

SELECT INVPLUS.STOCK, INVPLUS.TITLE
FROM INVPLUS
WHERE INVPLUS.SEQNO =
(SELECT MAX(Z.[SEQNO]) FROM INVPLUS AS Z
WHERE Z.STOCK = INVPLUS.STOCK);


John W. Vinson[MVP]

 

[Knox]

When I want to access this row, I do this cumbersome query which groups by
stock and finds the maximum sequence for each stock number. Then I have
another query which links to the first query by sequence number. I then
can
access the correct row.

Is there an easier way using the last function with grouping? In other
words, could I just do a group by stock, last on all the other fields,
order
by sequence and would that give me what I want? I've stayed away from
last
because I read somewhere it was not deterministic.

Last() returns the last record *in disk storage order* - which is
pretty much uncontrollable. What I'd suggest is a Subquery instead:

SELECT INVPLUS.STOCK, INVPLUS.TITLE
FROM INVPLUS
WHERE INVPLUS.SEQNO =
(SELECT MAX(Z.[SEQNO]) FROM INVPLUS AS Z
WHERE Z.STOCK = INVPLUS.STOCK);


John W. Vinson[MVP]

Disk Storage Order... Very interesting. That's so unusable, they should
take it out of the language! Thank you for explaining it and also for the
suggestion of the subquery.

Knox