By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,289 Members | 1,752 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,289 IT Pros & Developers. It's quick & easy.

Select based on field comparison

Expert 100+
P: 303
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:
Expand|Select|Wrap|Line Numbers
  1. Number     Version
  2. item1        1
  3. item1        2
  4. item2        1
  5. item2        2
  6. item2        3
  7.  
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?
Dec 10 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,661
It seems you need to GROUP BY the first field then return the Max() of the second.

Something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Number], Max([Version]) AS MaxVer
  2. FROM [YourTable]
  3. GROUP BY [Number]
Dec 10 '07 #2

Expert 100+
P: 303
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.
Expand|Select|Wrap|Line Numbers
  1. Number   Version   Qty
  2. item1      1       3
  3. item1      2       6
  4.  
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.
Dec 10 '07 #3

Expert 100+
P: 303
I can get the data I need by joining the table on select statements:
Expand|Select|Wrap|Line Numbers
  1. 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
  2.  
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.
Dec 10 '07 #4

NeoPa
Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. Number   Version   Qty
  2. item1      1       3
  3. item1      2       6
  4.  
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.
Dec 10 '07 #5

Expert 100+
P: 303
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.
Dec 10 '07 #6

NeoPa
Expert Mod 15k+
P: 31,661
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) :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Number],
  2.        Max([Version]) AS MaxVer, 
  3.        Val(Mid(Max(Format([Version],'0000') & [Qty]),5)) AS RelQty
  4. FROM [YourTable]
  5. 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.
Dec 10 '07 #7

Expert 100+
P: 303
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.
Dec 11 '07 #8

NeoPa
Expert Mod 15k+
P: 31,661
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.
Dec 11 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.