Max Seuqnce number with a Unique Reference?

[chris-uk-lad]

Hi all,

Im having trouble with a database query, i have a table that has a large list of Reference numbers, sometimes duplicates. These are indicated by another column that counts the number of duplicates (sequence of the numbers).

Ive been trying to create a quey that will retrieve the highest SEQNUM of each REFNUM. I tried a Max(SEQNUM) but got the below error.

"You tried to execute a query that does not include the specified expression 'REFNUM' as part of an aggregate function"

Can someone translate? I recreated the query on a simple 3 record 2 column database and same error

 

[gemma-the-husky]

dont think you can do it, and its a pig

you would think you could select other columns as information against the selected item - but you cant - you either get the message you got - or if you add a groupby, then it destroys what you are trying to do.

if you do the items one at a time, you can extract a query of each item (say sorted by your target expression field) - and if there are more than 1 record, then you can see which one you still want as the top item in the query.

Now someone will show us how!

 

[pbaldy]

It would look like the first query here:

http://www.baldyweb.com/LastValue.htm

 

[chris-uk-lad]

that works, dunno why but it does lol, only appears to be the reversal :x, thanks anyway

 

[pbaldy]

Does that mean it works? "Thanks anyway" implies that it doesn't.