Link to home
Start Free TrialLog in
Avatar of Basssque
Basssque

asked on

Oracle Query - Return results based on minimum value

The query below returns results that only equal the minimum PAY_CLASS which is 01.  How can I return only one result per EMPLOYEE_NO where their minimum PAY_CLASS could be higher than 01?  Thanks!

select *
from HRS.EMPLOYEE_PAYS
WHERE to_char(START_DATE, 'yyyy/mm/dd') > '2016/06/01' 
and to_char(END_DATE, 'yyyy/mm/dd') > to_char(sysdate, 'yyyy/mm/dd')
AND ACTUAL_FTE > 0
AND PAY_CLASS = (SELECT MIN(PAY_CLASS) FROM HRS.EMPLOYEE_PAYS)

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Didn't we cover ROW_NUMBER in some of your previous questions?  I can't remember...

Anyway, see if this is close to what you want.  It is untested.

Also, you should select just the columns you want and get out of the habit of "select *".

select col1, col2, col3
from (
	select col1, col2, col3, rownumber() over(partition by employee_no order by pay_class) rn
	from HRS.EMPLOYEE_PAYS
	WHERE to_char(START_DATE, 'yyyy/mm/dd') > '2016/06/01' 
	and to_char(END_DATE, 'yyyy/mm/dd') > to_char(sysdate, 'yyyy/mm/dd')
	AND ACTUAL_FTE > 0
	AND PAY_CLASS = (SELECT MIN(PAY_CLASS) FROM HRS.EMPLOYEE_PAYS)
)
where rn=1

Open in new window

>>to_char(START_DATE, 'yyyy/mm/dd') > '2016/06/01'

Didn't catch this until after I posted.

You shouldn't convert dates to strings for this type of check.  Compare dates to dates.  Dates are likely indexed and as soon as you use a function on them, Oracle cannot use the index.

...
WHERE START_DATE > to_date('2016/06/01' ,'yyyy/mm/dd')
      and END_DATE > trunc(sysdate)
...
Avatar of Basssque

ASKER

Yes we did go over it but in this case I would really prefer to use select * which I don't think I can use rownumber with.  There are like 100 columns I need to output, select * is ideal opposed to adding every column in manually.  
The date tip makes sense, thanks for that one!
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
As usual, thank you very much.  I really appreciate your time!
Just an observation - where startdate is greater than somedate (prior to the current date) AND greater than sysdate could be simply stated as where startdate is greater than sysdate. I think you  may mean something like where startdate is greater than somedate AND LESS THAN sysdate (or maybe trunc(sysdate + 1).
>>Just an observation - ...
There is a startdate and enddate?
DOH! I must get some new glasses :-)