Top 3 Records

[Bre-x]

Hi, Our Table (MAIN) has over 87,000 records. Here is the table structure:

SELECT
MAIN.Index,
MAIN.MTWORO_FINISH,
MAIN.MTWORO_WOPRE,
MAIN.MTWORO_WOSUF,
MAIN.MTWORO_OPER,
MAIN.MTWORO_OPER2,
MAIN.MTWORO_CODE,
MAIN.MTWORO_ACTHRS,
MAIN.MTWORO_ASETHRS,
MAIN.MTWORO_WC,
MAIN.MTWORO_WCDESC,
MAIN.MTWORO_STQTY,
MAIN.MTWORO_SQTY,
MAIN.MTWORO_DESC
FROM MAIN;

I need to query the top 3 Work Orders ( MTWORO_WOPRE, MTWORO_WOSUF) for
each Part ID (MTWORO_CODE) order by descending date(MTWORO_FINISH). Each
unique work order is made of two columns (MTWORO_WOPRE, MTWORO_WOSUF)

Could anyone help me with this query?

thank you all

 

[John Spencer]

The following should get you started or it may be the complete solution. I
assume that Index is a unique value and is the primary key.

SELECT MTWORO_WOPRE, MTWORO_WOSUF
FROM MAIN
WHERE Main.Index IN
(SELECT TOP 3 Tmp.Index
FROM MAIN as Tmp
WHERE Tmp.MTWORO_CODE = Main.MTWORO_CODE
ORDER BY Tmp.MTWORO_FINISH Desc, Tmp.Index)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

The problem is that this query is going to be slow. It has to run the
subquery once for each row in your table.

One thing you can do to speed it up is to make sure you have indexes on the
following three fields
MTWORO_FINISH
MTWORO_CODE
INDEX

Also, if you can limit the records it is running against at least to test to
see if you are getting the right data returned. Perhaps using

WHERE MtWORO_Code = "somevalue here"

Using a MAKE Table query is also slow.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Bre-x]

Hi,
Thank you for answering my post.
Yes, the Index is the primary key.


The query has been running for about an hour (during lounch time). I thing
there is something wrong with it.

SELECT MAIN.MTWORO_WOPRE, MAIN.MTWORO_WOSUF INTO JOHN
FROM MAIN
WHERE (((MAIN.Index) In (SELECT TOP 3 Tmp.Index
FROM MAIN as Tmp
WHERE Tmp.MTWORO_CODE = Main.MTWORO_CODE
ORDER BY Tmp.MTWORO_FINISH Desc, Tmp.Index)));

 

[Bre-x]

Hi,
Thanks again. I did make the modification that you suggested.

My query now is:

SELECT MTWORO_WOPRE, MTWORO_WOSUF
FROM MAIN
WHERE Main.Index IN
(SELECT TOP 3 Tmp.Index
FROM MAIN as Tmp
WHERE Tmp.MTWORO_CODE = Main.MTWORO_CODE and Tmp.MTWORO_CODE =
'01C09D-041202'
ORDER BY Tmp.MTWORO_FINISH Desc, Tmp.Index)

I got error: 3 records for that specific part

thnks

 

[Bre-x]

I got 3 records for that specific part

That is what I mean

thnks

 

[John Spencer]

Sorry, I wasn't clear. What I meant was

SELECT MTWORO_WOPRE, MTWORO_WOSUF
FROM MAIN
WHERE Main.Index IN
(SELECT TOP 3 Tmp.Index
FROM MAIN as Tmp
WHERE Tmp.MTWORO_CODE = Main.MTWORO_CODE
ORDER BY Tmp.MTWORO_FINISH Desc, Tmp.Index)
Main.MTWORO_CODE ='01C09D-041202'


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================