Tyler kyte dating
For example: select ename,sal from emp where sal = (select min(sal) from emp ) or sal = (select max(sal) from emp); It returns me the employees getting min and max sal valuesin the table emp. So for example, in Table A: SEQNO EFFECTIVE_DATE SERVICE_STATUS2 20020401011010091010030707070717 NPAnd in Table B: SERIAL_NUMBER SEQNO EFFECTIVE_DATE21814290378 2 2002042621814290378 2 2002013021814290378 2 2002012921814290378 2 2001100921814290378 2 2001100221814317918 2 2001091221814317918 2 200010289300134799 1 200303289300134799 1 200207229300134799 1 200207209300134799 1 20020719There should be a SERIAL_NUMBER field in Table A so that we can pin-point the EFFECTIVE_DATE. Table A should look like: SEQNO EFFECTIVE_DATE SERVICE_STATUS SERIAL_NUMBER2 20020426 ND 218142903782 20020130 AB 218142903782 20020129 AB 218142903782 20011009 AB 218142903782 20011002 AB 218142903782 20010912 AB 218143179182 20001028 AB 218143179182 20001026 NP 1 20030328 AB 93001347991 20020722 AB 93001347991 20020720 AB 93001347991 20020719 AB 93001347991 20020717 NP So the way this works is that for a SEQNO, you can have multiple serial numbers. As for the last question -- hopefully you see now that there is NO SUCH THING as the n'th row in a relation database (unless you yourself number them!
My question is "Can this query be put using the In operator like select ename,sal from emp where sal in('query1','query2');" query1 deals with min(sal) and query2 deals with max(sal). We need to produce a report that looks like: SERIAL_NUMBER SRV_BEGIN------------------------------------9300134799 2002071721814290378 2001100221814317918 20001026The logic is this: For each sequence number, list the effective dates of each serial number. ) You can get the n'th row from a result set -- but the n'th row can and will change over time.
That would give us 12 rows (using my example) with many duplicates.
The distinct will remove the duplicate ITEM_NO/BIN combinations and then the sum collapses out the BIN dimension.
For example, for this data set, I need to grab the record (quantity) from record highest shipment_date but then I want to grab lowest shipment date (initial date). The reason why I have itthrougth my code is because I don't want to do a lot joins. Anyway, here is the function I called get chemist from the previousselect. FUNCTION get_chemist (p_job IN VARCHAR2) RETURN VARCHAR2 IS v_emp_id VARCHAR2 (35); v_emp VARCHAR2 (50); BEGIN SELECT employee_fk_id INTO v_emp_id FROM job_chemists WHERE job_fk_id = TRIM (p_job) AND record_status = 'CURRENT'; SELECT usid INTO v_emp FROM employees WHERE TRIM (employee_id) = TRIM (v_emp_id); RETURN v_emp; END September 12, 2003 - pm UTC umm, so you think this would be better then a join??????
REQUEST_ID STOCK_NUMB BACKORDER_QUANTITY SHIPMENT_DATE---------- ---------- ------------------ -------------------- 593 AC010 190 24-jun-2003 593 AC010 145 25-jun-2003 593 BB005 380 24-jun-2003 593 BB005 300 25-jun-2003 594 AC010 90 24-jun-2003 594 BB005 50 24-jun-2003 594 MS193 50 24-jun-2003 i WOULD GET FOR AC010: 593 AC010 145 24-jun-2003 or 593 AC010 145 25-jun-2003 24-jun-2003 (here the min(date) is added as another column. it is a join -- just a really really really slow join, the slowest way to do it.don't second guess the database engine, it is pretty good at doing what it does -- joins!
select a.request_id,a.shipment_id,b.item_number,b.stock_number, 2 max(a.shipment_date) from shipment a, shipped_item b 3 where a.shipment_id = b.shipment_id and b.disposition='B' 4 group by a.request_id,a.shipment_id,stock_number,item_number; REQUEST_ID SHIPMENT_ID ITEM_NUMBER STOCK_NUMB MAX(A.
If you are at the conference, drop into the Groundbreaker area and say Hello.What i am tring to get is the records of the shipment of the max date. Every shipment record that has a backorder flag, I want to read the quantity.However if it is the same item and request id I am only intersted in the last shipped one.Check out all our database development sessions at OOW19 Thanks for the question, Munzer.Asked: March 17, 2002 - pm UTC Answered by: Tom Kyte - Last updated: April 24, 2016 - am UTC Category: Application Server - Version: 8.1.7 Viewed 100K times!
The effective date should be the earliest date for that serial number. I am hoping that you can come up with a single SQL statement to do this. David September 13, 2003 - am UTC no, i meantfor every row -- where the status is NOT "NP" -- update that column to the first value for that sequence you find in the other matches your output. Only by numbering the rows yourself and using order by can you get the same n'th row time after time after time select * from ( select a.*, rownum r from ( query-to-define-your-result-set ) a where rownum desc emp Name Null?