"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!

 

[Golom]

SELECT tbl_DefectsCarrie.DEFECTNUM, 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

WHERE ORDERNUM = (Select Max(tbl_DefectEvtsCarrie.ORDERNUM)
                  FROM tbl_DefectEvtsCarrie)
      AND tbl_DefectsCarrie.DEFECTNUM=670 

ORDER BY Max(tbl_DefectEvtsCarrie.ORDERNUM);

 

[CarrieR]

Thank you very much for responding. However, I get an error message "you tried to execute a query that does not include the specified expressono "DEFECTNUM' as part of an aggregate function.

 

[PHV]

Another way, replace this:
SELECT tbl_DefectsCarrie.DEFECTNUM
with this:
SELECT TOP 1 tbl_DefectsCarrie.DEFECTNUM
and this:
ORDER BY Max(tbl_DefectEvtsCarrie.ORDERNUM);
with this:
ORDER BY 2 DESC;

SELECT TOP 1 D.DEFECTNUM, Max(E.ORDERNUM) AS MaxOfORDERNUM, U.LastName
FROM tbl_DefectsCarrie D INNER JOIN (
tbl_CarrieUser U INNER JOIN tbl_DefectEvtsCarrie E ON U.IDRecord = E.ASGNDUSERS
) ON D.IDRECORD = E.PARENTID
WHERE D.DEFECTNUM = 670
GROUP BY D.DEFECTNUM, U.LastName
ORDER BY 2 DESC;

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

 

[CarrieR]

Thanks

This works. You guys are great.

 

[CarrieR]

Ok, this worked for record #670. The table tbl_DefectsCarrie (or "D") has hundreds of records. I need to know if we can loop through this table, pull out for each DefectNum the highest Ordernum from table tbl_DefectEvtsCarrie ("E") and give me the corresponding Last name associated with this record??

 

[CarrieR]

PHV:

Can you help me in looping thru records to pull data as mentioned above?

Thanks

Carrie

 

[lespaul]

Carrie,

Please refer to thread701-1157436 for the best way to present the information to get the answer you need (second to last post).

I'm sure if you present your information in that manner you will get the exact query you need. It's not really necessary to loop through the results, there are ways to get the max for each name, but if you show us the "picture", it's much easier to help.

Leslie

 

[CarrieR]

lespaul:

All works correctly in the response from PHV, except "WHERE D.DEFECTNUM = 670" i need it to show all defectnums.

If you need more info will gladly provide...

 

[lespaul]

with this query:

SELECT TOP 1 D.DEFECTNUM, Max(E.ORDERNUM) AS MaxOfORDERNUM, U.LastName
FROM tbl_DefectsCarrie D INNER JOIN (
tbl_CarrieUser U INNER JOIN tbl_DefectEvtsCarrie E ON U.IDRecord = E.ASGNDUSERS
) ON D.IDRECORD = E.PARENTID
WHERE D.DEFECTNUM = 670
GROUP BY D.DEFECTNUM, U.LastName
ORDER BY 2 DESC;

you will only get records where the defectnum = 670 and you will only get 1 record with the TOP 1.

I believe what you need is a correlated subquery in order to get the Max orderNum for EACH defectNum. In order to help you create the correlated subquery, we really need to know how the one to many relationship is set up.

For instance, one employee can have many skills, but each person can only have one best skill:

Employee
EmpID
EmpName

Skills
SkillID
SkillName

EmployeeSkills
EmpID
SkillID
SkillRating

Now if you want only the top skill for each employee, you need a correlated subquery:

SELECT EmpName, SkillName FROM EmployeeSkills
INNER JOIN Employee on EmployeeSkills.EmpID = Employee.EmpID
INNER JOIN Skills on EMployeeSkills.SkillID = Skills.SkillID
INNER JOIN (SELECT EmpID, SkillID FROM EMployeeSkills WHERE SkillRating = 1) A on A.EmpID = EmployeeSkills.EmpID

This will return a SINGLE record for each employee stating their 1 top skill.

I believe you also need a query like this (all the defects with just the max order number), but without knowing all the pieces, it's a little hard to help you set it up.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[CarrieR]

OK, we have 3 tables:

tbl_DefectsCarrie
ID Record
DefectNum

tbl_DefectEvtsCarrie
ID Record
Parent ID (linked to tbl_DefectsCarrie ID Record)
OrderNum
Asgnduser

tbl_CarrieUser
ID Record (Linked to tbl_DefectEvtsCarrie (Asgnduser)
Last Name

Scenario:

I have one Defect Number (670 for example) with multiple defect events. I am trying to get the max (highest) Order Num, then the assigned user for that last record.

 

[PHV]

Perhaps something like this ?
SELECT D.DefectNum, M.MaxOfOrderNum, U.LastName
FROM ((tbl_DefectsCarrie D
INNER JOIN tbl_DefectEvtsCarrie E ON D.[ID Record] = E.[Parent ID])
INNER JOIN (
SELECT [Parent ID], Max(OrderNum) AS MaxOfOrderNum
FROM tbl_DefectEvtsCarrie GROUP BY [Parent ID]
) M ON E.[Parent ID] = M.[Parent ID] AND E.OrderNum = M.MaxOfOrderNum)
INNER JOIN tbl_CarrieUser U ON E.Asgnduser = U.[ID Record]

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

 

[CarrieR]

Will check it out, so far looks correct. I will take a look at some of the records to verify....

 

[CarrieR]

PH

Works great if there are multiple assigned to, however, if there is only one assigned to person that is not showing.

C

 

[PHV]

Sorry I don't see why (apart broken referential integrity ...)

 

[CarrieR]

Some issues have only been assigned to one person (so far). Potential is unlimited to reassign to other people. I had to make a table to dump data into to play around with, currently as one user to many issues, actually s/b many to many, this is the way the external database was set up. (They also used a "memo" field to store numeric values, hence had to create tables to store data into to link with.)

 

[PHV]

I assumed that in tbl_DefectsCarrie ID Record and DefectNum are uniquely related.

 

[CarrieR]

DefectNum not indexed.

 

[PHV]

I agree, but may one DefectNum have many ID Record (or one ID Record have many DefectNum) ?

Anyway, posting some sample values with expected results versus actual results would help.

 

[CarrieR]

One defect num = one id record and vice versa.

This is what I am trying to get to:

Def Num 1 Assign to: Jones Date Assigned: 1/1/03
Def Num 1 Assign to: Smith Date Assigned: 2/23/05

Above works correctly, I get 2nd record as that is the current assigned to.

If we have;

Def Num 3 Assigned To: Robinson Date Assigned: 3/2/04

The single assigned to record is not showing; the order num is used to count assigned to, that part is OK, but the asgndusers field should be not null, then pull the single assigned to name for that issue.