Query by Date

[Bre-x]

I ask this question last week, but I didn't explain myself very well.

I have a table with over 187,000 records. From Jan 2001 to today. This table
contains Work Order Information.
These are the main Columns (Table has 15 Columns): Finish Date, WO Prefix,
WO Suffix, Part ID, Sequence.

I need to query some info: for each Part ID, I need the last 3 Work Orders
order by Finish Date (Descending)

Note that a Work Order made of two columns (Prefix and Suffix): 4343-1,
4343-2, etc, etc each of these records is unique, and at least 2,000 unique
Parts.

Thank you all in advance.

Bre-x

 

[Guest]

Try this --
SELECT T.[Part ID], T.[WO Prefix], T.[WO Suffix], (SELECT COUNT(*)
FROM [bre-x] T1
WHERE T1.[Part ID] = T.[Part ID]
AND T1.[Finish Date] >= T.[Finish Date]) AS Rank, T.[Finish Date]
FROM [bre-x] AS T
WHERE ((((SELECT COUNT(*)
FROM [bre-x] T1
WHERE T1.[Part ID] = T.[Part ID]
AND T1.[Finish Date] >= T.[Finish Date])) Between 1 And 3))
ORDER BY T.[Part ID], T.[Finish Date] DESC;

 

[Bre-x]

Hi,
Thanks for answering my post. I have a question. I dont understand your
query

This is my table structure:

MAIN.Index,
MAIN.MTWORO_FINISH,
MAIN.MTWORO_WOPRE,
MAIN.MTWORO_WOSUF,
MAIN.MTWORO_OPER,
MAIN.MTWORO_OPER2,
MAIN.MTWORO_CODE, (Part ID)
MAIN.MTWORO_ACTHRS,
MAIN.MTWORO_ASETHRS,
MAIN.MTWORO_WC,
MAIN.MTWORO_WCDESC,
MAIN.MTWORO_STQTY,
MAIN.MTWORO_SQTY,
MAIN.MTWORO_DESC


This is the query??

SELECT MAIN.[Part ID], MAIN.[MTWORO_WOPRE], MAIN.[MTWORO_WOSUF], (SELECT
COUNT(*)
FROM [bre-x] T1
WHERE T1.[MTWORO_CODE] = MAIN.[MTWORO_CODE]
AND T1.[MTWORO_FINISH] >= MAIN.[MTWORO_FINISH]) AS Rank,
MAIN.[MTWORO_FINISH]
FROM [bre-x] AS MAIN
WHERE ((((SELECT COUNT(*)
FROM [bre-x] T1
WHERE T1.[MTWORO_CODE] = MAIN.[MTWORO_CODE]
AND T1.[MTWORO_FINISH] >= MAIN.[MTWORO_FINISH])) Between 1 And 3))
ORDER BY MAIN.[MTWORO_CODE], MAIN.[MTWORO_FINISH] DESC;

this is your suggestion:

SELECT T.[Part ID], T.[WO Prefix], T.[WO Suffix], (SELECT COUNT(*)
FROM [bre-x] T1
WHERE T1.[Part ID] = T.[Part ID]
AND T1.[Finish Date] >= T.[Finish Date]) AS Rank, T.[Finish Date]
FROM [bre-x] AS T
WHERE ((((SELECT COUNT(*)
FROM [bre-x] T1
WHERE T1.[Part ID] = T.[Part ID]
AND T1.[Finish Date] >= T.[Finish Date])) Between 1 And 3))
ORDER BY T.[Part ID], T.[Finish Date] DESC;

Regards,

Bre-x