I had this example stored in my notes, but have never tried it:
First determine if odd or even rows by doing a count into a variable, then:
SET VAR vleft = (MOD(.vNumRows,2)) (if 0, then even rows)
Find out where halfway is: SET VAR vhalf INT = ((.vrows + 1) / 2)
If even rows:
SEL AVG(DISTINCT cost) INTO vaverage FROM products t1 WHERE +
.vhalf >= (SEL COUNT(*) FROM products t2 WHERE t2.cost > t1.cost) AND+
.vhalf >= (SEL COUNT(*) FROM products t2 WHERE t2.cost < t1.cost)
If odd rows:
SEL AVG(DISTINCT cost) INTO vaverage FROM products t1 WHERE +
.vhalf > (SEL COUNT(*) FROM products t2 WHERE t2.cost > t1.cost) AND+
.vhalf > (SEL COUNT(*) FROM products t2 WHERE t2.cost < t1.cost)
Karen
