Using both "Group By" and "Order By"

[#1]

Using a table like the following:

LOAD FirstValue(name) INLINE [
    id, type, name, age
    1, man, dave, 65
    2, man, chris, 66
    3, woman, mary, 64
    4, girl, karen, 27
    5, woman, betty, 60
    6, boy, john, 22
    7, man, peter, 40
    8, boy, jay, 21
    9, girl, sandra, 20
    10, man, bob, 23
] Group by type Order by age;

To get a listbox like this:

• bob
• betty
• sandra
• jay

But the script dont allow me to use Group By and Order By at the same time, any ideas?

best regards.

[#2]

Sort the listbox itself by age. Go to the sort tab and put in an expression like avg(age) and tell it to sort ascending.

[#3]

Does that mean you can't combine the two commands (Order By and Group By) in the same statement? What you suggested is OK but not the same thing, whould like to solve it using the script dialog.

Thank you!

[#4]

I think the only place you can use them both is from a resident table. You could load the inline data & group it into a resident table then pull it out of there in the order you prefer. I would only do that if there is further analysis you need to do on the data based on that load order within the script.

You will still need to address how it sort's in the app itself & go back into the sort tab and force it to sort on load order. Otherwise it will default to numeric ascending / alpha ascending.

[nagaiank]

The following script works.

Tab1:

LOAD * INLINE [

    id, type, name, age

    1, man, dave, 65

    2, man, chris, 66

    3, woman, mary, 64

    4, girl, karen, 27

    5, woman, betty, 60

    6, boy, john, 22

    7, man, peter, 40

    8, boy, jay, 21

    9, girl, sandra, 20

    10, man, bob, 23

];

Tab2:

NoConcatenate LOAD * Resident Tab1 Order By type, age;

DROP Table Tab1;

Tab3:

LOAD FirstValue(name) as Firstname Resident Tab2 Group By type;

[#6]

If you use group by, it is not allowed (nor logical) to use any fields outside the group without aggregation. All records belonging to the same group are reduced to one row. The field age does not have a single value as different members to that group can have different ages. What you can do is:

order by Avg(age)

[#7]

Plz check the attcahed app.

Hope it helps...