Pull top three newest Dates by PartID

[Vagus14]

Hey all,

I am trying to create a query that would pull the top three recent dates for a part along with any extra data I need to add.

I know how to pull the Last and First but I am confused how I would pull the top three by most recent date.

Next, I would like to count them by the part it pulls, for example.
PART: 12345, Number: 1, Date: 10/10/2016
PART: 12345, Number: 2, Date: 9/10/2016
PART: 12345, Number: 3, Date: 8/10/2016
PART: 123456, Number: 1, Date: 9/10/2016
PART: 123456, Number: 2, Date: 7/10/2016
PART: 123456, Number: 3, Date: 3/10/2016

If someone could help with the SQL coding of this, that would be awesome. I am new to SQL still and I know basic SQL.

 

[pbaldy]

See if Allen's method works for you:

http://allenbrowne.com/subquery-01.html#TopN

 

[Vagus14]

Thank you for this site, it's getting me to the next steps. I put together some of my own code based on his model. I am pulling data from an SQL warehouse, there's many records. As you can see below I have limited the data pulled by the following:
1. Plant
2. Date

I am trying to pull these three columns in:
1. SOCreateDt
2. PN_Ext
3. Plant
4. SOUnitPrice
5. CustomerName

I am experiencing long query time. Is there anything I can do to improve the performance?

Code below:

SELECT dbo_SalesOrders.SOCreateDt, dbo_SalesOrders.PN_Ext, dbo_SalesOrders.Plant, dbo_SalesOrders.SOUnitPrice, dbo_SalesOrders.CustomerName
FROM dbo_SalesOrders
WHERE (((dbo_SalesOrders.SOCreateDt) Between Now()-5 And Now()) AND ((dbo_SalesOrders.PN_Ext) In (SELECT TOP 3 PN_Ext FROM dbo_SalesOrders AS Dupe
ORDER BY Dupe.SOCreateDt DESC)) AND ((dbo_SalesOrders.Plant)="2110") AND ((dbo_SalesOrders.SOUnitPrice)<>0))
ORDER BY dbo_SalesOrders.SOCreateDt, dbo_SalesOrders.PN_Ext;

 

[Vagus14]

Following up on this. Does this work well with a SQL table?

 

[pbaldy]

I don't have a lot of experience using this, but I do have it in a car maintenance db and it works fine, though I use in in a stored procedure so it runs in SQL Server. You might try creating it as a stored procedure or view and see if that helps.

 

[Vagus14]

Thank you for the response and help so far Pbaldy. I am new to VBA procedures and coding. Do you have an example of the code? I am guessing you would code the module as a function?

 

[pbaldy]

Stored procedures and views are objects within SQL Server, so you'd need the capability to create them on the server. Whichever you used would not exist in Access, it would be called from Access.

 

[Vagus14]

Oh very interesting, I have never tried tried to called a SQL procedure before.

The first step I need to do is determine how to create an outside SQL procedure. How would I do this? I believe maybe IT would have to help create the procedure as these tables pull from SAP, is this logic correct?

I found some info on the SQL module in access through a search, how would I create the SQL procedure?

http://stackoverflow.com/questions/18800727/ms-access-call-sql-server-stored-procedure

 

[pbaldy]

Not sure of your situation. I have the access and knowledge to create these on our servers myself. I know many others have to go through IT departments to get them created. There's no guarantee it would be faster, so my first step would be to test your SQL on the server, converting to T-SQL (the flavor of SQL used by SQL Server). I don't know if that's something you can do or IT has to. You basically want to find out if forcing the processing to happen on the server makes it run faster.

 

[Vagus14]

OK I don't really use SQL outside of Access so it's a bit different, any idea how I would translate the data above into a MySQL format? I tried it but it gave me an error when I tried the LIMIT 5 instead of TOP 5. Let's start simple. How would I get SQL to pull the TOP 5? It's giving me an error.

SELECT TOP 5 * 
FROM SG_WRITE_USER.Z_R3_XXXX_TBL;

I also tried:

SELECT * 
FROM SG_WRITE_USER.Z_R3_XXXX_TBL
LIMIT 5;

 

[pbaldy]

SQL Server or MySQL? I haven't worked much with MySQL, not sure of the syntax. TOP would still work in SQL Server.

 

[Minty]

The second syntax is correct for MySql, but I'm not sure about the table name structure. It doesn't look right to me.