Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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 .