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)
>>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)
...
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)
...
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!
The date tip makes sense, thanks for that one!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
There is a startdate and enddate?
DOH! I must get some new glasses :-)
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 *".
Open in new window