I'm sure there is a way to do what I'm looking for in SQL, but I'm stuck trying to figure this out. I have a query that basically looks like this (there are other fields that are not relevant to this):
ItemNum Sort1 Sort2 For each ItemNum there are multiple records giving the sort order that falls into the other two columns. So you might have values like this: Item1 - 1 - 1 Item2 - 1 - 2 Item1 - 2 - 1 Item2 - 2 - 2 Item3 - 1 - 1 And so on. These sort values are coming from foreign tables with linked information, they don't relate directly to the Items, just how I need to sort them for a particular function. I need to group similar items together, but order them according to the sort values. Like this: Item1 - 1 - 1 Item1 - 2 - 2 Item2 - 1 - 2 Item2 - 2 - 1 Item3 - 1 - 3 Or even just the items listed in the order that they end up being sorted is fine as well: Item1 Item2 Item3 If I order by the sort fields, I get the first set of results, doing a GROUP BY Item is the same. So I know I need to do a sub-query or something else to get the desired result. Ideas? --- Mary Jo ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3265 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.6
