Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kris19
Starting Member
2 Posts |
 Posted - 2014-05-08 : 07:15:44
|
| Hi All,Could you please help in resolving this issue:I have a query:-SELECT DISTINCT BH.BST_BESTELLUNG AS PO_NUMBER,BH.BST_LIEFERANT AS SUPPLIER,BZ.BDT_REFNUMMER AS ITEM_REF, BZ.BDT_ANZ_RESTMENGE AS OUTSTANDING_QTY, BZ.BDT_EKP_BESTELLT AS PURCHASE_PRICE, BH.BST_WAEHRUNG AS CURRENCY FROM BESTHEAD BHINNER JOIN BESTZEIL BZ ON BH.BST_ORIGNR=BZ.BDT_ORIGNR AND BH.BST_BESTELLUNG=BZ.BDT_BESTELLUNGWHEREBST_FILIALE = 150ANDBST_STATUS IN(1,2) ANDBZ.BDT_ANZ_RESTMENGE > 0This is the result set:PO_NUMBER SUPPLIER ITEM_REF OUTSTANDING_QTY PURCHASE_PRICE CURRENCY51310 100198 350990 6 36.42 £51310 100198 419574 4 28.75 £51310 100198 419598 2 28.75 £51310 100198 419628 2 32.58 £51317 100235 469500 1 61.90 £51317 100235 479844 16 85.91 £51319 100275 451314 7 43.36 £51322 100297 300773 1 292.00 £51323 100308 488686 5 80.36 £51323 100308 488747 5 80.36 £Expected Result:PO_NUMBER SUPPLIER ITEM_REF OUTSTANDING_QTY PURCHASE_PRICE CURRENCY51310 100198 419628 2 32.58 £51317 100235 469500 1 61.90 £51319 100275 451314 7 43.36 £51322 100297 300773 1 292.00 £51323 100308 488686 5 80.36 £How to display Distinct PO_Number values |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-05-08 : 07:58:21
|
| Consider the first line in your expected output. It shows 51310 as the PO_NUMBER, but 419628 as the SUPPLIER. How should the query choose that SUPPLIER among the four that are present? Your answer will determine how to phrase the query to get the desired results. |
 |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2014-05-08 : 08:02:17
|
How do you decide which row fom table BESTZEIL you want to see? I can see no logic and no good reason.
Too old to Rock'n'Roll too young to die. |
|
|
|
|
|
|
Distinct column value from multiple coulmns