Group By / MAX Function

[LilMissAttack]

I thought I was headed in the right direction with this but can't quite figure it out.

I have 3 tables. One is a list of all manufactured items. The 2nd table lists all the components for those manufactured items. For instance, a manufactured item could simply be it's own item, or could be made up of several components..sometimes 10 or more. In the 3rd table, I have the lead time of each of those components.

I want to pull the list of manufactured items and the component with the longest lead time for each manufactured item. Then I would like it to pull the 2nd longest lead time. Is this best done with grouping and the Max function?

Thanks!

[Robeen]

You can Group By [ManufacturedItem] and Order By [LeadTime] Desc . . .

That should give you what you need - if I understand your requirement correctly.

[rpeare]

Are you looking at only pulling the two longest lead times for each manufactured item or are you looking to sort the entire list of components from largest lead time to lowest lead time?

If it's the latter you can follow robeen's suggestion

If it's the former, create robeen's query and where it says SELECT in the sql statement of your query type in SELECT TOP 2 and it will show you only the two longest lead time items for a specific assembly.