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

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

HarryCallaghan
Starting Member

5 Posts

Posted - 07/09/2013 :  06:27:40  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1050 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.

Cheers
MIK
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 07/09/2013 :  06:59:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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