"Max" number query problems......

[CarrieR]

Hello

Based on the below SQL statement, I am trying to only show the max order # (4) and the related last name, not all the last names.

DEFECTNUM MaxOfORDERNUM LastName
670 1 Dietz
670 2 Sandahl
670 4 Harig

I only want in my query to show the last name of "harig" as it is the highest order Number.

I have read many many postings on this site related to this but can't figure out how to get this to only show what I want (highest order number and related name). Note: this database is from an external application called Test Tracker, I pulled the tables in with ODBC links.


SELECT tbl_DefectsCarrie.DEFECTNUM, Max(tbl_DefectEvtsCarrie.ORDERNUM) AS MaxOfORDERNUM, tbl_CarrieUser.LastName
FROM tbl_DefectsCarrie INNER JOIN (tbl_CarrieUser INNER JOIN tbl_DefectEvtsCarrie ON tbl_CarrieUser.IDRecord = tbl_DefectEvtsCarrie.ASGNDUSERS) ON tbl_DefectsCarrie.IDRECORD = tbl_DefectEvtsCarrie.PARENTID
GROUP BY tbl_DefectsCarrie.DEFECTNUM, tbl_CarrieUser.LastName
HAVING (((tbl_DefectsCarrie.DEFECTNUM)=670))
ORDER BY Max(tbl_DefectEvtsCarrie.ORDERNUM);

Can anybody help me???

Thanks!

 

[PHV]

Def Num 3 Assigned To: Robinson Date Assigned: 3/2/04
Can you please post the values in the 3 tables for this defect ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CarrieR]

PH

Defect Order tbl_Defects Asgnd tbl_CarrieUser LastNm
Num Num Carrie.IDRec User .IDRecord

515 1 521 19 19 Jones
515 2 521 37 37 Smith
515 3 521 91 91 White
557 1 563 2 2 Clark

Record #515, working correctly in my query to show "White" as last order # for this record is 3. Record #557 only has one assigned to, one order num, "Clark" not showing up at all.

Carrie

 

[PHV]

And the Parent ID ?

 

[CarrieR]

Sorry, same as field above tbl_DefectsCarrie.IDRecord.

 

[PHV]

Here the result of my query with the data you posted:[tt]
DefectNum MaxOfOrderNum LastName
515 3 White
557 1 Clark[/tt]

So, I don't understand your issue...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CarrieR]

SELECT D.DefectNum, M.MaxOfOrderNum, U.LastName
FROM ((tbl_DefectsCarrie D
INNER JOIN tbl_DefectEvtsCarrie E ON D.[IDRecord] = E.[ParentID])
INNER JOIN (
SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]
) M ON E.[ParentID] = M.[ParentID] AND E.OrderNum = M.MaxOfOrderNum)
INNER JOIN tbl_CarrieUser U ON E.Asgndusers = U.[IDRecord]

I am still not getting the record with only 1 order number....what am I doing wrong???

 

[PHV]

Debug step by step.
1) Is this query working as expected ?
SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]

2) And this ?
SELECT E.[ParentID], E.OrderNum, E.Asgndusers
FROM tbl_DefectEvtsCarrie E
INNER JOIN (
SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]
) M ON E.[ParentID] = M.[ParentID] AND E.OrderNum = M.MaxOfOrderNum)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CarrieR]

PHV

You have been such a help for me, but I still am having trouble getting the record with only one order number. Below is the raw data, note defectnum 557 has maxofordernum of 1, name Harig.

DEFECTNUM MaxOfORDERNUM LastName
1 1 Miller
515 1 Bennett
515 12 Sandahl
515 13 Smith
515 3 Tabora
557 1 Harig
670 1 Dietz
670 4 Harig
670 2 Sandahl

Sql as below:

SELECT D.DEFECTNUM, M.MaxOfOrderNum, U.LastName
FROM tbl_DefectsCarrie AS D INNER JOIN (tbl_CarrieUser AS U INNER JOIN (tbl_DefectEvtsCarrie AS E INNER JOIN [SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]
]. AS M ON (E.ORDERNUM = M.MaxOfOrderNum) AND (E.PARENTID = M.ParentID)) ON U.IDRecord = E.ASGNDUSERS) ON D.IDRECORD = E.PARENTID;


Results of sql query:

DEFECTNUM MaxOfOrderNum LastName
1 1 Miller
670 4 Harig
515 13 Smith

I am still not getting the record #557 and can't figure out why. The pressure is on for me to get this working correctly..can you help me? Will email sample database if will help you to understand situation....

 

[CarrieR]

Sorry wrong data in 1st table, s/b

DEFECTNUM MaxOfORDERNUM LastName
1 2 Deview
1 1 Miller
515 1 Bennett
515 12 Sandahl
515 13 Smith
515 3 Tabora
557 1 Harig
670 1 Dietz
670 4 Harig
670 2 Sandahl

Results of Query:

DEFECTNUM MaxOfOrderNum LastName
1 2 Deview
670 4 Harig
515 13 Smith

Carrie

 

[PHV]

You didn't answer my 2 questions.
Your posted data are inconsistent with the previous.

 

[CarrieR]

My 2nd email today is the correct data. The query works correctly if there is more than one order number. If there is only 1, it does not show this record. Sql as above. Can you help me?

 

[CarrieR]

Debug step by step.
1) Is this query working as expected ?
SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]

Above works correctly, result below.

ParentID MaxOfOrderNum
7 2
521 13
563 4
676 4

2) And this ?
SELECT E.[ParentID], E.OrderNum, E.Asgndusers
FROM tbl_DefectEvtsCarrie E
INNER JOIN (
SELECT [ParentID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [ParentID]
) M ON E.[ParentID] = M.[ParentID] AND E.OrderNum = M.MaxOfOrderNum)

Incorrect data as below.

ParentID OrderNum Asgndusers
7 2 23
563 4
676 4 2
521 13 97

There s/b a value of "4" for record # 563.

 

[PHV]

Can you please post the result of this query ?
SELECT D.DefectNum, D.IDRecord, E.IDRecord, E.ParentID, E.OrderNum, E.Asgndusers, U.IDRecord, U.LastName
FROM (tbl_DefectsCarrie D
INNER JOIN tbl_DefectEvtsCarrie E ON D.IDRecord = E.ParentID)
INNER JOIN tbl_CarrieUser U ON E.Asgndusers = U.IDRecord

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CarrieR]

PH

I think I just figured it out, but do not know how to correct. The below table shows all the order numbers for 4 DefectNums, #1, 515, 557 and 670. EvtDefid will define an assigned to action; 1 indicates assigned to. The problem with DefectNum 557 is when we use max or order num "4" that is not true for this defect, as there was only one assigned to, asgneusers #2. The other 3 records had multiple assigned to's, to determine the last one is the ordernum field. For example DefectNum1 has 2 order numbers, 1 & 2. The most current assignee is order number 2.

Defect Parent Order Evt Asgnd
Num ID Num Defid Users
1 7 1 1 32
1 7 2 1 23
515 521 11 5
515 521 1 1 19
515 521 2 1 91
515 521 3 1 37
515 521 4 1 91
515 521 5 3
515 521 6 1 97
515 521 7 5
515 521 8 1 91
515 521 10 1 97
515 521 12 1 91
515 521 13 1 97
515 521 9 3
557 563 1 1 2
557 563 2 11
557 563 3 11
557 563 4 11
670 676 4 1 2
670 676 2 1 91
670 676 1 1 24
670 676 3 11

Here is how it should look:

Defect Parent Order Evt Asgnd
Num ID Num Defid Users
1 7 2 1 23
515 521 13 1 97
557 563 1 1 2
670 676 4 1 2


I apologize for all the confusion. In going back over months of notes I realized that the evtdefid field "1" indicates an assigned to.

 

[PHV]

SELECT D.DefectNum, M.MaxOfOrderNum, U.LastName
FROM ((tbl_DefectsCarrie D
INNER JOIN tbl_DefectEvtsCarrie E ON D.IDRecord = E.ParentID)
INNER JOIN (
SELECT ParentID, Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie WHERE EvtDefid=1 GROUP BY ParentID
) M ON E.ParentID = M.ParentID AND E.OrderNum = M.MaxOfOrderNum)
INNER JOIN tbl_CarrieUser U ON E.Asgndusers = U.IDRecord

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CarrieR]

It looks like it works! I love you guys.....