Tyler kyte dating setting up a dating site
Can u please Clarify a bit more why r u using Distinct and then first_value here.. Thanks and Best Regards,pascal March 19, 2002 - am UTC Well, I'm actually using the ANALYTIC function and then the distinct.We got -- for every row in T (where there are more then one row for each ITEM_NO, BIN combination) the first_value of qty when sorted descending by date.Effective date is the sysdate when record is inserted.Inventory: Item_no Qty Bin Effective_Date AC006 10 DC001 2/1/2002AC006 20 DC002 2/1/2002AC006 100 DC001 5/1/2002AC006 50 DC002 5/2/2002AC006 30 DC003 5/3/2002AC006 20 DC008 5/4/2002I need to calculate two things:1. which I basically did by taking qty of the item in inventory with the max(effective_Date) total_received and stored in that bin since that max(effective_date) total shipped from that bin since that max(effective_date).2. Here I need to look at the highest effective date for an item at a bin.
The first is the best in my opinion (easy to read, performs well)[email protected] The next uses a "max" trick i use alot to avoid correlated subqueries:[email protected] Refer to ur Query just compied from above:select item_no, sum(qty) 2 from ( 3 select distinct item_no, bin, 4 first_value(qty) over(partition by item_no, bin 5 order by effective_date desc) qty 6 from t 7 ) 8 group by item_no 9 /I have been reading ur Book and been trying these Analytic Functions myself.sometimes i had to use Distinct to get correct results ---otherwise , it doesn't work..Bascially I need to run a SQL statement that gives me 200(sum last 4 recrods) for total of an item AC006. How would a computer know that -- you are using some hidden piece of knowledge in this case.How do you formulate that statement and exclude all the previous inventories for that item that are out of date.3. Would it still work where I basically stored the items in different bins. I don't even know what that hidden bit of knowledge is myself.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.
SO here the result should be so that I skipthe first and third records. REASON_FOR_CHANGE, 11 DECODE(STATUS,'CANCELLED',jobs. MODIFIED_BY, NULL) MODIFIED_BY 12 FROM JOBS, job_chemists, employees, enotebook_reviews 13 -- where is not null AND jobs. RECORD_STATUS='CURRENT' 15 and = job_fk_id 16 and job_chemists. RECORD_STATUS='CURRENT' 18 and job_chemists.employee_fk_id = employee_id 19 and e_notebook_entity_id = 20 and job_fk_id = e_notebook_entity_id 21 / 611 rows selected.