Getting value based on max of other fields

Question

Getting value based on max of other fields

philcart

SSC-Forever

Points: 47796
More actions
September 23, 2002 at 1:20 am

#78870
Hope someone has an idea to kickstart me with a problem I'm tackling.
The business requirement (which I've inserted field names into) ,
"Select the Record Key (C_KEY) for the supplier (N_SUP) from the product (N_PROD) with the largest stock (A_STK) and order (A_ORD) volume."
The data for one supplier looks like,
```
N_SUP   N_PROD       C_KEY A_STK     A_ORD

------- ------------ ----- --------- -------

108     110953216    9900  -4161     0

108     345305838    4539  -298343   0

108     531514289          1260      0

108     557438528          1998      0

108     586080246          5         0

108     900309775    4539  -71649    75000

108     965004876    0000  405000    0

108     984753338    0000  24167     0

108     984788231    0000  12013     0
```
We have over 50,000 unique supplier numbers with over 250,000 product numbers.
Basically what is required back is a recordset containing every supplier and the applicable record key.
Thanks in advance
Phill
--------------------
Colt 45 - the original point and click interface

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

I'm not entirely certain how to translate the business req. "largest stock and order volume", but I chose to translate it to A_STK + A_ORD. If that's correct, then maybe something like this might help you:



SELECT x.N_SUP, x.C_KEY

FROM PRODTABLE x

INNER JOIN (SELECT N_SUP, MAX(A_STK + A_ORD) AS bar

            FROM PRODTABLE

            GROUP BY N_SUP) AS foo

  ON x.N_SUP = foo.N_SUP

  AND x.A_STK + x.A_ORD = foo.bar

Note that I have used the name PRODTABLE as the name of the table you're querying, and that this query will return duplicate suppliers if there are several products for a supplier with the same A_STK + A_ORD.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

Edit: Fixed code indentation

Edited by - chrhedga on 09/23/2002 02:58:02 AM

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Antares686 SSC Guru Points: 125444 More actions · Answer 2

To understand this properly can you give me what the output should be from your data posted and why?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

philcart SSC-Forever Points: 47796 More actions · Answer 3

quote:
To understand this properly can you give me what the output should be from your data posted and why?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

From the data posted, I would expect the return to be,



N_SUP   C_KEY

------- ------- 

108     0000

This is because product number 965004876 has the largest stock and order volume.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

Prakash Heda SSCertifiable Points: 5539 More actions · Answer 4

Product number 965004876 has the largest stock but order volume is 0 in this case????

Prakash

Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 5

quote:
This is because product number 965004876 has the largest stock and order volume.

OK, so then you do mean tstock + order volume, right? Then my example code should work for you.

Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

http://www.sql.nu

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/