September 30, 2008 at 8:03 pm
Hi to All,
Need your help very badly, I have use the Max function in my previous query and it works, all column data are the same except for 1 Row. So I get the recent data.
My problem now is I have data but now it have 2 Row Diffrence, I used another Max function to the other field it shows the data but not the correct row it show the MAX value of the row and not the recent value.
Your reply is highly appreciated! Below is my query.
The MAX(T0.DocNum) gets the recent data which is correct, but when I added the MAX(T1.Price) it show the maximum value of the price and not the recent data.
MAX(T0.DocNum),
T0.DocDate,
T0.DocDueDate,
T0.U_GRPOSsheet,
T0.CardCode,
T0.CardName,
T1.TargetType,
T1.ItemCode,
T1.Dscription,
T1.U_grpoGrossWT,
T1.U_grpoSackWT,
T1.Quantity,
T1.U_grpoWTDeduction,
MAX (T1.Price)
FROM
dbo.OPDN T0
INNER JOIN dbo.PDN1 T1 ON (T0.DocEntry = T1.DocEntry)
Thank you very much!
October 1, 2008 at 12:37 pm
Max
Returns the maximum value in the expression.
October 1, 2008 at 5:13 pm
Hi,
Yes I know the function MAX, but it seems it cant get the correct data in the row of Price, If I will used the MAX in my Field DocNum it will display the recent or the Highest value but in terms of the price it gets the MAX value, in which the value supposed to be is the value of the result of MAX(DocNum) but the value of Price is from not MAX.
It very hard to explain but I hope it will claries all it up.
Thank you.
October 2, 2008 at 7:23 am
Hi
I don't think MAX is going to work on the price field. If I understand correctly from the select statement that you provided the Price column contains the actual price. This is going to cause a problem as the Price might not always be higher with every new entry. MAX is going to work for the DocNum because the new entries will always be higher than the previous entry.
If you want to get duplicates for a certain doc number where the price differs the following might help:
SELECT DISTINCT DocNum, Price
FROM Table
where DocNum IN (SELECT DocNum FROM Table GROUP BY DocNum HAVING count(Price) > 1)
October 2, 2008 at 9:53 am
you probably have somthing in your table like this
1,5
2,3
if you take the max of each coloumn, you get 2,5 not 2,3
October 2, 2008 at 5:14 pm
Hi Christo,
Yes that's my point at last I deliver it correctly for you to understand, I will try this query. Thats correct point of view of yours, I can get all the recent DocNum but in the Price it will not since it will capture the MAX value of the Price which is not corresponding with the DocNum data.
Thank you very much! Just update you for the result.
Regards,
Clint
October 2, 2008 at 5:28 pm
Hi SSC,
Sorry I mean row if you take you example it will take like this :
DocNum Price
1 5
1 3
2 4
2 2
Max(Docnum) the result must be base on the DocNum:
2 4
2 2
In my case it showing the wrong result which is:
2 5
2 3
Hope it will clarify. Thank you very much in advance.
Clint
October 3, 2008 at 6:44 am
clint_pow (10/2/2008)
Hi SSC,Sorry I mean row if you take you example it will take like this :
DocNum Price
1 5
1 3
2 4
2 2
Max(Docnum) the result must be base on the DocNum:
2 4
2 2
In my case it showing the wrong result which is:
2 5
2 3
Hope it will clarify. Thank you very much in advance.
Clint
If those are the results you are looking for then why do you need to do Max(Price) in the first place?
Also, if you want to get better answers read the following article: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 3, 2008 at 5:17 pm
Hi SSC,
If I will do the MAX(Price), the data will get the MAX(Value) of the field, example
1 4
1 5
2 3
2 2
I want to show from the list the MAX(DocNum) which is 2,2, I want also the Price value is 3,2. I want to based the MAX(DocNum) following with their corresponding Price value, if I will used the MAX(Price) it will get the 5,4 which resulted to :
2 4
2 5
The correct must be
2 3
2 2,
I have example only two data, in the case above I have many records , the records are very the same except for the Field DocNum and Price, sample:
DocNum Price Date Desc Unit
1 4 1/1/1900 Good pc
1 5 1/1/1900 Good pc
2 2 1/1/1900 Good pc
2 3 1/1/1900 Good pc
Thank you!
October 4, 2008 at 6:34 pm
clint_pow (10/3/2008)
Hi SSC,If I will do the MAX(Price), the data will get the MAX(Value) of the field, example
1 4
1 5
2 3
2 2
I want to show from the list the MAX(DocNum) which is 2,2, I want also the Price value is 3,2. I want to based the MAX(DocNum) following with their corresponding Price value, if I will used the MAX(Price) it will get the 5,4 which resulted to :
2 4
2 5
The correct must be
2 3
2 2,
I have example only two data, in the case above I have many records , the records are very the same except for the Field DocNum and Price, sample:
DocNum Price Date Desc Unit
1 4 1/1/1900 Good pc
1 5 1/1/1900 Good pc
2 2 1/1/1900 Good pc
2 3 1/1/1900 Good pc
Thank you!
I'm still not 100% sure I'm understanding your problem correctly.
Are you looking for all prices for the MAX DocNum?
If so you can try something like this.
Please note that this code is un-tested because you have not provided sample DDL and data.
SELECT T0.DocNum,
T0.DocDate,
T0.DocDueDate,
T0.U_GRPOSsheet,
T0.CardCode,
T0.CardName,
T1.TargetType,
T1.ItemCode,
T1.Dscription,
T1.U_grpoGrossWT,
T1.U_grpoSackWT,
T1.Quantity,
T1.U_grpoWTDeduction,
T1.Price
FROM dbo.OPDN T0 WITH (NOLOCK)
INNER JOIN dbo.PDN1 T1 WITH (NOLOCK) ON T0.DocEntry = T1.DocEntry
WHERE T0.DocNum IN (SELECT MAX(DocNum) FROM dbo.OPDN)
October 5, 2008 at 5:26 pm
HI SSC,
At last you have solve the problem, the MAX(DocNum) must be in the WHERE clause statement not on the SELECT statement it filter it all.
Thank you very much!
regards,
Clint
October 6, 2008 at 2:22 am
Hi
If I understand this correctly he wants to retrieve all the duplicates in the table based on DocNo. If this is the case then the queery I provided above should work, giving all the DocNos with multiple prices.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply