TOP/MAX help - 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

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

TOP/MAX help

Author

Topic

Plaice
Starting Member

20 Posts

Posted - 2010-06-15 : 11:40:41

Have a current query which all works fine but I've been asked to add more data but only very select data. Current query looks like this:

SELECT        actions.partnumber, actions.outsqty, actions.comment, actions.bomcomment, stock.desc1, stock.desc2, stock.desc3, stock.desc4, actions.duedate, stock.supplier, 
                         stock.stdcost, stock.alias01, stock.alias02, stock.alias03
FROM            stock, actions
WHERE        stock.partnumber = actions.partnumber
ORDER BY actions.partnumber, actions.duedate

Now the problem I have is the extra data I've been asked to pull in is in another table (the plines table).

Which with my rough workings now looks like this:

SELECT        actions.partnumber, actions.outsqty, actions.comment, actions.bomcomment, stock.desc1, stock.desc2, stock.desc3, stock.desc4, actions.duedate, stock.supplier, 
                         stock.stdcost, stock.alias01, stock.alias02, stock.alias03, plines.pordno, plines.plineno, plines.qty, plines.duedate AS Expr1, plines.price, plines.enteredby
FROM            stock, actions, plines
WHERE        stock.partnumber = actions.partnumber AND actions.partnumber = plines.partnumber
ORDER BY actions.partnumber, actions.duedate

Which is all fine but the problem I'm having is they only want to see the last 3 entries in plines.pordno, and the relevant data in the rest on the plines table, for each partnumber.

Now I think the the TOP function might be the best to use as MAX would only show me one line but I have no idea how to implement it in the query.

Probably not the best worded question but any help gratefully received. Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2010-06-15 : 11:58:36

OK....is your background Oracle???

In any case do you know what ANSI SQL is??

And when you say the last three entries...how do YOU define that...and for the love of GOD, please don't tell us it's the last ones inserted

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Plaice
Starting Member

20 Posts

Posted - 2010-06-16 : 05:51:02

The background is a Foxpro database unfortunately. I hate anything to do with it to be honest but it's what I have to work with for now.

The last three entries can be defined either two ways:

Using plines.plineno. This value is an incremental value that increases so could take the top three values for these.

Or

Could possibly use plines.duedate and take the last three dates from there.

Prefer to use the first as that would give the last three true entries but going by date would also work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources