Query to filter next to last oper

[Cam]

Hello,

I am trying to create a query to pull the next to last operation for all the
part# but not sure how to achieve this.
There are part# with several operations ranging from 10 to 999 or 997 and
number of operations vary by part#. Thanks
Here is the example data:

Part# Oper Other......
12A1260 10
12A1260 20
12A1260 35
12A1260 230
12A1260 999
05T1000 10
05T1000 50
05T1000 90
05T1000 997

Result wanted:
12A1260 230
05T1000 90

 

[Ken Snell [MVP]]

Assuming Oper field is a numeric field:

SELECT [Part#], Max(Oper) AS Oper2ndToLast
FROM TableName
WHERE Oper <
(SELECT Max(T.Oper) FROM TableName AS T
WHERE T.[Part#] = TableName.[Part#])
GROUP BY [Part#];

 

[Duane Hookom]

Try something like:
SELECT PartNum, Oper, Other
FROM Operations
WHERE Oper =
(SELECT TOP 1 Oper
FROM Operations O
WHERE O.PartNum = Operations.PartNum and O.Oper < Operations.Oper
ORDER BY O.Oper DESC)

 

[KARL DEWEY]

Try this --
SELECT A.[Part#], A.Oper, A.x
FROM tblCam AS A INNER JOIN (SELECT TOP 2 AA.Oper FROM (SELECT TOP 4
Y.Oper FROM tblCam AS Y ORDER BY Y.Oper DESC) AS AA ORDER BY AA.Oper) AS BB
ON A.Oper = BB.Oper;

 

[Cam]

Thanks All, Worked great.

Assuming Oper field is a numeric field:

SELECT [Part#], Max(Oper) AS Oper2ndToLast
FROM TableName
WHERE Oper <
(SELECT Max(T.Oper) FROM TableName AS T
WHERE T.[Part#] = TableName.[Part#])
GROUP BY [Part#];

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hello,

I am trying to create a query to pull the next to last operation for all
the
part# but not sure how to achieve this.
There are part# with several operations ranging from 10 to 999 or 997 and
number of operations vary by part#. Thanks
Here is the example data:

Part# Oper Other......
12A1260 10
12A1260 20
12A1260 35
12A1260 230
12A1260 999
05T1000 10
05T1000 50
05T1000 90
05T1000 997

Result wanted:
12A1260 230
05T1000 90