Sort recordset before aggregating

[esteiner]

Hello,

I would like to sort the results of my select statement before using aggregate functions such as the 'First' or 'Last'. How do I accomplish this?

Here is my current SQL:

SELECT pull_inv_BRE.ManfPartNum_NP,
First(pull_inv_BRE.ManfPartNum) AS FirstOfManfPartNum,
First(pull_inv_BRE.CorePartNum) AS FirstOfCorePartNum,
Sum(pull_inv_BRE.Quantity) AS Quantity
FROM pull_inv_BRE
WHERE (((pull_inv_BRE.ManfPartNum_NP) Like "am27128a" & "*") AND ((pull_inv_BRE.Description)<>"MENTOR"))
GROUP BY pull_inv_BRE.ManfPartNum_NP;


From this query, I would like to sort by pull_inv_BRE.ManfPartNum before using the 'First' function.

For example, ManfPartNum has two possible values:

"ABCD-2F1" and "ABCD2F1"

They both represent the same part number, but I would prefer to use the record with the "-" in it. That is why I want to to sort the records first and use the 'First' function.

 

[lespaul]

do all the parts you want have the "-"?

I don't think you can do what you want using these techniques.



Leslie

 

[esteiner]

No, some of them also have other special characters like "/" or "#" or ":" etc.

I want to use the values for the records that have the special characters. That is why I want to sort the recordset first so that I can use the 'First' function. the special characters appear at the top of the list if you sort ascending. Does this make sense?

-Charlie

 

[PHV]

Have you tried the Min (or Max) function instead of the unreliable First ?

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

 

[MichaelRed]

Does't make any difference. Group By will not "delete" any values, so both of your examples would remain. Using "First", you could return either, depenning on the parameter used.




MichaelRed