​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Grouping without using a GROUP BY 


Here's a way to get the max/min of a column, grouping by another column without using a GROUP BY. A warning first :- Don't use this with big tables as it may take an age to run. Ok here goes, say you have the following table:-

part_num        number_sold                   date_sold
-------------        ------------------      ---------------
1                         17                           01-jun-02
1                          5                            02-jun-02
1                          99                          10-jul-02
2                          2                            18-apr-02
3                          12                           20-may-02
5                          12                           14-aug-01
5                          1                             15-aug-01

You would like to get the part_num max(number_sold) and date_sold
from this table i.e

part_num        number_sold                   date_sold
-------------         ------------------     ----------------------
1                       99                              10-jul-02
2                       2                                18-apr-02
3                      12                               20-may-02
5                      12                               14-aug-01

Its not immediately obvious how you would do this even using a GROUP BY but the following will work:-

select t1.part_num,t1.number_sold,t1.date_sold
from mytable t1
where not exists (select part_number from mytable
                              where part_number = t1.part_number
                              and number_sold > t1.number_sold)


To get the min change the '>' to a '<'