Aggregate function in set logic

[#1]

Hi,

I have following data sample:

Table 1

Order id        item id               price

1                    x                       20

1                    y                       30

1                    z                       40

Item id        Owner id               Name

x                     123                    John

y                     124                   Harrry

z                     125                    Neo

I need to count number of order ids where price given by specific owner was minimum, maximum or 2nd (1 order id can have only 3 item ids)

I have tried the following query:

count(distinct {$< "aggr(firstsortedvalue(Price),-[Item ID]),[Order ID])"

={"$aggr(min(Price),[Trip ID])"}

>} [Trip ID])

My intention is when I filter out a certain name(say Neo), I would get:

Agent  Name             #Order Ids where price is lowest                #Order Ids where price is 2nd    #Order Ids where price is max

Neo                                       some number                                     some number                               some number

Thanks for your help

[sunny_talwar]

Would you be able to provide some additional sample? I am not entirely sure as to what is needed. May be provide actual numbers instead of some number for the expected output as well.

Best,

Sunny

[#3]

Hi Sunny,

This might be more clear:

Order id        item id               price

1                    x                       20

1                    y                       30

1                    z                       40

2                    a                       10

2                    b                       5

2                    c                       50

Item id        Owner id               Name

x                     123                    John

y                     124                    Harrry

z                     125                    Neo

a                     127                    Neo

b                     129                    Rocky

c                      130                   Rohan

Agent  Name        #Order Ids where price is lowest       #Order Ids where price is 2nd    #Order Ids where price is max

Neo                                           0                                                           1                                              1

Every order id will have 3 or more items, each item has ann owner.
Problem is to find the number of order ids where specific owners's quoted item was 1st , 2nd and third.

Thanks .