Select based on field comparison

[improvcornartist]

I am trying to select all table data with a restriction based on a comparison of fields within the table. I'm not sure how to describe the query very well, so I'll give an example of what I need. The table setup is similar to the following:

Number     Version
item1        1
item1        2
item2        1
item2        2
item2        3
 

I need my query to return item1 version 2 and item2 version 3 (the record for each item with the largest version number). Can anyone help?

[NeoPa]

It seems you need to GROUP BY the first field then return the Max() of the second.

Something like :

SELECT [Number], Max([Version]) AS MaxVer
FROM [YourTable]
GROUP BY [Number]

[improvcornartist]

Thanks. Now I need to add a third field. What I need in the end is Select Field1, Field2, Field3 From Table Where Field2 is the maximum value of Field2 in relation to Field1.

Number   Version   Qty
item1      1       3
item1      2       6
 

I have to be able to get the specific Qty for the largest Version for each item in the table. This is what's giving me the most trouble.

[improvcornartist]

I can get the data I need by joining the table on select statements:

Select T2.Number, T2.Qty From (Select Number, Max(Version) as V From Table Group By Number) as T1, (Select Number, Version, Qty From Table) as T2 Where T1.Number = T2.Number and T1.V = T2.Version
 

But is this really the best way to get the data? I also need to tie in 3 other tables to get everything I need.

[NeoPa]

Thanks. Now I need to add a third field. What I need in the end is Select Field1, Field2, Field3 From Table Where Field2 is the maximum value of Field2 in relation to Field1.

Number   Version   Qty
item1      1       3
item1      2       6
 

I have to be able to get the specific Qty for the largest Version for each item in the table. This is what's giving me the most trouble.

You don't seem to explain what data you want out when you add the extra field into the equation. You don't share the relationship you're looking for for a field you refer to as both Field3 & Qty. If you can specify that clearly and unambiguously then I can try to answer your question.

[improvcornartist]

You don't seem to explain what data you want out when you add the extra field into the equation.

The query I provided in my last post is the exact data I want out when I add the extra field.

You don't share the relationship you're looking for for a field you refer to as both Field3 & Qty.

I'm not sure I understand you. I'm not looking for a relationship. The only thing that matters is the version number. I just meant that I need the specific Qty in the largest version number record. Sorry for the ambiguity, I used field3 to express the idea of what I needed, where Qty is what the field actually is.

The table contains many item numbers. Each number has 1 or more revisions. Each number + revision has it's own quantity. I need to get every numbers highest revision and the quantity for that revision. The query I posted gets what I need. It just seems like there should be a better way of getting the same data.

[NeoPa]

That explains the relationship (between Revision & Qty) quite well thank you.
This is something that is not natively supported in SQL but there are ways of extracting the data you need.
You're looking for the [Qty] value that matches the maximum [Version] value in the recordset for each item ([Number]). I usually concatenate the two values in a field and then strip out the unrequired parts to leave the value required. Perhaps the following SQL will explain the concept better (It's also what I think you need in your scenario) :

SELECT [Number],
       Max([Version]) AS MaxVer, 
       Val(Mid(Max(Format([Version],'0000') & [Qty]),5)) AS RelQty
FROM [YourTable]
GROUP BY [Number]

It assumes the value of [Version] will never exceed 9,999. The trouble with this method is that it does rely on manipulating the results somewhat, and requires foreknowledge of the format of the expected data. If neither is a problem, then this method will work fine for you.

PS.

The query I provided in my last post is the exact data I want out when I add the extra field.

There are two problems with that approach :

1. It puts the responsibility on me to do your work. Not nice when you're asking for assistance.
2. It only works if your SQL is correct. I have no reason to doubt it in this case, but I can also do without checking it, as an answer that relies on the correctness of the question is not a very clever concept.

[improvcornartist]

Sorry. I did not intend for you to do my work. I only wanted to know if there was a better approach. The query I posted was just to show what I needed and how I approached getting it (I tried solving my problem).

Your query is a better solution, thank you for spending the time to help me. The Version will always be formatted like '000', and unless I'm missing something, manipulating the results shouldn't cause a problem.

Thanks again, and sorry for being difficult. I do appreciate your help.

[NeoPa]

Not a problem. It was clearly not a deliberate thing so no issue (You just caught me on a bad day ;)).
I'm glad my version was able to help.
BTW it's a concept I find useful so often, so I'd keep a note of the idea at least.