>Ok, just so that I understand. The reason that the above was not working for Perry was because originally he had group by fld1, fld2, fld3 but his order by clause was not included and his order by clause needed to be fld1, fld2, fld3, fld4. Being different meant that it needed to be included.
>
>But there was another post that said the order by was the last thing to occur so therefore it should have no effect. It only orders the resultant set. Is that true?
Dan,
I couldn't browse the whole thread so I don't have an idea about the failing SQL. On my part I do so many typos while writing SQL and think he made one too.
As I did one here too :)
group by f1,f2,f3 ;
order by f1,f2,f3,f4
AND
group by f1,f2,f3
are still equivalant. Because grouping would yield unique values for f4 (the one entered last in table), including it in order would have no extra effect. What I mean here :
f1 f2 f3 f4
A A A 5
..
A A A 4
..
A A A 7
..
A A A 6
....
select * from mytable ;
group by f1,f2,f3
f1 f2 f3 f4
A A A 6
This is how "group by" looks into a table. If you want 4 (min(f4)) or 7 (max(f4)) in result then you need aggregate functions instead of plain f4
select f1, f2, f3, ;
min(f4), max(f4), cnt(*) from mytable ;
group by 1,2,3
"Order by" occurs last and orders result set - true AFAIK. Whatever the "order by", first an implicit order by is done for "group by" so you never get two values for a particular group. If no "order by" is present, that implicit ordering is the one that's presented in result set.
Cetin