Finding min()

[wcleve]

I'm having problems...

Here's the table:

Equipment
ID AMPS LINE
10 956
10 750
10 1000 795 A
20 1000
20 1200 820 A
20 956

What I need to do is find the minimum amps for each id along with the amps for the line:

ID AMPS LINE_AMPS
10 750 1000
20 956 1200

I've tried to use a subquery but got the error "You have written a subquery that can return more than one field without using the EXISTS reserved word...", then tried to return to one field and got null results.

Help!!
TIA
Wendy

 

[Golom]

In looking at your input table and the expected query results, it appears that you are selecting the minimum and maximum value for "AMPS" for each "ID". If that is correct then try

SELECT ID, MIN(AMPS) as [AMPS], MAX(AMPS) as [LINE_AMPS]
FROM EQUIPMENT As E
GROUP BY E.ID
ORDER BY E.ID

 

[wcleve]

Golom,
Thank you for your response. LINE_AMPS will not always be the maximum number of AMPS. LINE_AMPS will always be the number of AMPS associated with the figure in LINE for each SECTION. In other words, there is only one LINE value for each SECTION in which I need to return the AMPS for that LINE (LINE_AMPS). Hope this is clear.

Thanks.

 

[Golom]

Gotcha!

SELECT ID, MIN(AMPS) as [AMPS],
(Select X.AMPS From Equipment As X Where NOT(X.Line IS NULL) AND X.ID = E.ID) As [LINE_AMPS]
FROM EQUIPMENT As E
GROUP BY E.ID
ORDER BY E.ID

I'm assuming that there is only one non-null value of "LINE" associated with each "ID" and all the rest of the "LINE" values for an "ID" value are NULL. If that's not the case then you may need a construct like "Len(X.Line) =0" in place of the "NOT ... IS NULL" filter.

 

[Golom]

Sorry about that. I guess I S**d up in copying your field names. It should be

SELECT E.ID, MIN(E.AMPS) AS AMPS, (Select X.AMPS From Equipment As X Where NOT(X.Line_Amps IS NULL) AND X.ID = E.ID) AS LINE_AMPS
FROM EQUIPMENT AS E
GROUP BY E.ID
ORDER BY E.ID;

 

[wcleve]

That did it! Golom, You're wonderful!!!!!