Please start any new threads on our new site at http://forums.sqlteam.com. 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!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Year Problem Query
 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
2875 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
52326 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
17689 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
52326 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  
 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.07 seconds. Powered By: Snitz Forums 2000