quick question

[sfullman]

I'll try and make this question concise.

To present some records in the right order, I MUST sort by their CreateDate. I'm using GROUP BY, and it's a join via a primary/foreign key.

The way I'm grouping (by the primary table ID), all of the ID's for the group are obviously the same. Since it's really the same record, just joined to several "child" records in the other table, the CreateDate's are going to be the same for the group.

Now, I know that when I group by, I lose the properties of the individual elements, but..

let's say that there's another date for each "child" record. Is there any way I can ensure that the MAX date or MIN date is sorted on top? This tells me a lot and I wouldn't have to do a query.

Remember, I've already used up my sort on making sure the GROUP's order is correct.

Thanks,
Sam

[tomhath]

Post a simple example of what the input and output data would look like.

Remember that you can't group 3 rows from the child table, but then select one of those three rows as the one at the "top"; if the parent row and three child rows are joined and grouped, they must all be combined into one row of the result set.

You can use an ORDER BY after the GROUP BY. But I get the feeling you're mixing up something in the JOIN and GROUP BY logic.