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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Year Problem Query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 01/05/2013 :  07:40:44  Show Profile  Reply with Quote
SELECT TOP (5) Table_PurchaseOrder_Information.PO_Number, Table_PurchaseOrder_Information.Supplier, Table_PurchaseOrder_List.Unit_Price,
Table_PurchaseOrder_Information.CreatedDate
FROM Table_PurchaseOrder_Information INNER JOIN
Table_PurchaseOrder_List ON Table_PurchaseOrder_Information.ReferenceNumber = Table_PurchaseOrder_List.ReferenceNumber
ORDER BY Table_PurchaseOrder_Information.CreatedDate DESC

thats my query... top 5 2012 records will show... but i have created a new record for 2013 but it wont show..?
any help on these?

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/05/2013 :  16:17:32  Show Profile  Reply with Quote
There would have to be an entry in each table for the 2013 record to show.

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/06/2013 :  10:30:53  Show Profile  Reply with Quote
are you looking at top 5 values for each year?
then use

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY YEAR(Table_PurchaseOrder_Information.CreatedDate) ORDER BY Table_PurchaseOrder_Information.CreatedDate DESC) AS Seq,
Table_PurchaseOrder_Information.PO_Number, Table_PurchaseOrder_Information.Supplier, Table_PurchaseOrder_List.Unit_Price, 
Table_PurchaseOrder_Information.CreatedDate
FROM Table_PurchaseOrder_Information INNER JOIN
Table_PurchaseOrder_List ON Table_PurchaseOrder_Information.ReferenceNumber = Table_PurchaseOrder_List.ReferenceNumber
)t
WHERE Seq<=5
ORDER BY CreatedDate DESC


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Yonkouturko
Yak Posting Veteran

Philippines
59 Posts

Posted - 01/06/2013 :  20:48:33  Show Profile  Reply with Quote
@Visakh16
im looking for top 5 of the whole record regardless of the year...

i hope your getting me clear on this

thanks...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17595 Posts

Posted - 01/07/2013 :  04:32:43  Show Profile  Reply with Quote
do you have the corresponding record created in Table_PurchaseOrder_List for 2013 also ? Or only in the Table_PurchaseOrder_Information ?


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/07/2013 :  10:11:00  Show Profile  Reply with Quote
then only other issue might be that you dont have matching entry in Table_PurchaseOrder_List table for 2013 records. Try changing INNER to LEFT JOIN

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.05 seconds. Powered By: Snitz Forums 2000