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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

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
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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
Kyle AbrahamsSenior .Net DeveloperCommented:
can you post your whole query?  And what your sample results should be?
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
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

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.