RaqForum 28 No.
554 View  • 
     ...

Intra-group Ranking

 
SQL(179)

【Question】

I have a table of the following structure:
Date   Item   Size   Price   Quantity   TakenBy
2015-01-01   USB   32G   35   1   Lee
2015-01-01   USB   64G   30   2   Lee
2015-01-01   USB   32G   35   1   Audrey
2015-01-01   hard disk   500G   300   3   Audrey
2015-02-01   USB   500G   300   3   Audrey
I need to get the following result in SQL.
rank table of January:
Item   TakenBy   Quantity   Amount   QRank   ARank
USB   Lee   3   95   1   1
USB   Audrey   1   35   2   2
hard disk   Audrey   3   300   1   1

 

【Answer】

You can use rank function within a database that supports analytic functions to perform this intra-group ranking. Or you can do this in SPL (Structured Process Language) with a universal, simple two-liner:

A

1

$select Item,TakenBy,sum(Price*Quantity)   Amount,sum(Quantity) Quantity from tb group by Item,TakenBy order by Item,Quantity   desc

2

=A1.derive(ranki(Quantity;Item):QRank,QRank:ARank)

A1: Group records by Item and TakenBy and calculate quantity and amount for each group; the result set is sorted by Item and Quantity.

A2: Add QRank column and ARank column and calculate their values for each item taken by a certain person.

See How to Call an SPL Script in Java to learn how to call an SPL script from a Java application.


SQL(179)
 
 •  554 View  •