Help with a query - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a query

Author

Topic

HarryCallaghan
Starting Member

5 Posts

Posted - 2013-07-09 : 06:27:40

Hello,

First of all, i apologize for the subject of the topic which is obviously very unespecefic.

My problem is: Lets say i have two tables.

1) Articles. With a "Part No" field and a "Code" field
2) Sales. With a "Part no" field, which is the same than in the articles table, and a date field. There are many more fields, but lets assume there are only those.

I have many lines with Codes duplicates for diferent Part No, and i want filter just showing the line for the most recent sale for the Code.

For example if i have
---Articles ---- ---Sales----
Part No - Code Part No -Sale Date
X 1 X 2013
Y 1 Y 2012

I want to get only the article with Part No X.

Thanks a lot!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-09 : 06:56:17

If,partNo is joining criteria for the two tables and you're looking for only one most recent record, then may be following is something you're looking for

SELECT Top 1 a.PartNo,a.Code,b.PartNo,b.SaleDate
FROM Articles a
inner join Sales b on a.PartNo=b.PartNo
order by SaleDate desc

If not, then provide some more real data (provide it in consumable format e.g. insert statments) for both tables, and the desired ouput you're looking for then.

Cheers
MIK

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-09 : 06:59:17

Sounds like this to me


SELECT PartNo,Code,SaleDate
FROM
(
SELECT a.PartNo,a.Code,b.SaleDate,
ROW_NUMBER() OVER (PARTITION BY a.Code order by b.SaleDate desc) AS Seq
FROM Articles a
inner join Sales b on a.PartNo=b.PartNo 
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources