Convert Access Sumary Query to Oracle Pass Through Query

[Ron Weller]

I have a summary query in Access which has several Fields that use the First Or Last options. There is no equivolent function in Oracle. Does anyone know how to do this in oracle?

Simplified version of Access Query:
SELECT CustName, First(AcctType), Last(SalesPerson), Sum(MonthlySales) FROM CustSales WHERE SalesMonth Is Not Null And SalesYear = 2007 GROUP BY CustName;

[cjard]

It's because first and last dont really make sense without some kind of order.

I suggest that you use analytical functions:

http://www.ss64.com/orasyntax/an_firstlast.html


For example:

SELECT department_id,
       MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct) "Worst",
       MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct) "Best"
FROM employees
GROUP BY department_id;