Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
How can I use order by and group by together. Order by only works using a resident load. When I try to take the short cut
Load *
Resident XYZ
Order by [Received Date] asc;
And then another load statement to group, this doesnt work.
In the resident load, do I need to specify all fields and not use Load *?
Can I use Load * when I do the group by part?
Because this seems like a long repetition of the script.
Thanks
H
The order by needs to come after the group by:
LOAD
FieldA,
FieldB,
sum(FieldC) as SumC
GROUP BY FieldA, FieldB
ORDER BY FieldB desc;
The order by needs to come after the group by:
LOAD
FieldA,
FieldB,
sum(FieldC) as SumC
GROUP BY FieldA, FieldB
ORDER BY FieldB desc;
You can use Group by and Order by in Resident load. All the columns mentioned in Load except aggregated columns should be included in Group by clause
Load Col1,Col2,Max(Col3)
Resident XYZ
Group by Col1,Col2
Order by Col1 desc,Col2 asc;
Resident load is works as view when u can select * it will take all columns from previously loaded table.
if u want to take some selected column u are able to select it.
if u do not want previous table or field simply drop it to avoid repetation.
Thanks
Herbert,
If you want to group by and order by, you first have to group by, loading this field, and then with a new statement, ordering by it. Then, drop it this field you don't need it anymore.
Hope it helps.
Marc.
Thanks Gysbert