SQL query

Hello,

Need help with SQL query:

SELECT     PartNum, TranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranNum, TranType, InvAdjReason
FROM        Erp.PartTran
GROUP BY PartNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranNum, TranType, InvAdjReason
HAVING     (PartNum = N'171') AND (TranType = N'ADJ-CST') AND (InvAdjReason = N'stdch')


I need group query by "PARTNUM" - 171 is just for test and show max "TRANNUM". Doesn't matter date or any other criteria, need see last transaction for part only.

See attached graphics.
example.JPG
henryk123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Don't group by trannum and max it:
SELECT     PartNum, max(TranNum) MaxTranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranNum, TranType, InvAdjReason
FROM        Erp.PartTran
GROUP BY PartNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranType, InvAdjReason
HAVING     (PartNum = N'171') AND (TranType = N'ADJ-CST') AND (InvAdjReason = N'stdch')

Open in new window

0
henryk123Author Commented:
Tried before. Working.. but give me two records. Why ? I have no idea.
I want see only last one means 10408 (the are in sequential order)
See attached.

henry
example1.jpg
0
Kyle AbrahamsSenior .Net DeveloperCommented:
HAVING     (PartNum = N'171') AND (TranType = N'ADJ-CST') AND (InvAdjReason = N'stdch')  and TranNum = Max(TranNum)

or you can do:

select top 1
<rest of your query>
order by trannum desc
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

henryk123Author Commented:
Working good but only for one part 171. If I remove filter for partnum (show me all parts) I got only one line means this is my top 1.

henry
0
Kyle AbrahamsSenior .Net DeveloperCommented:
can you post your whole query?  And what your sample results should be?
0
henryk123Author Commented:
SELECT     TranNum, PartNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranType, InvAdjReason
FROM        Erp.PartTran
WHERE     (TranType = N'ADJ-CST') AND (InvAdjReason = N'stdch')
ORDER BY PartNum



See attached. I should get only what is marked in red for each part number. One record for each part # with highest "TranNum"
example.xlsx
0
Kyle AbrahamsSenior .Net DeveloperCommented:
;with cte as
(
SELECT     PartNum, TranNum, TranDate, MtlUnitCost, LbrUnitCost, BurUnitCost, SubUnitCost, MtlBurUnitCost, TranNum, TranType, InvAdjReason, row_number() over (partition by partnum order by trannum desc) rowNum
FROM        Erp.PartTran
)

select * from cte
where rowNum = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.