Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help with a query
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

5 Posts

Posted - 07/09/2013 :  06:27:40  Show Profile  Reply with Quote

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!

Flowing Fount of Yak Knowledge

1054 Posts

Posted - 07/09/2013 :  06:56:17  Show Profile  Reply with Quote
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.

Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 07/09/2013 :  06:59:17  Show Profile  Reply with Quote
Sounds like this to me

SELECT PartNo,Code,SaleDate
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 

SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000