Retrieving Max / Latest Date From A Table With A Join To One Another Table
Sep 19, 2011
I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.
Here is what I have:
selectMAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
from cs join wr on cs.applicant_id=wr.applicant_id
where wr.status_cd='AC'
group by cs.applicant_id
I am trying to retrieving the latest create_date and pay_total for each act_id from the table. Table is having more than 5000000 records. I prepared the below query but it didn't the data even after 6 hours.
select a.act_id,a.create_DATE,a.Pay_TOTAL from payment_tab a where 1 = (select count(distinct b.create_DATE) from payment_tab b where b.act_id=a.act_id and b.create_DATE >= a.create_DATE)
I would like to retrieve only the latest repair information based on the latest date regardless of the other information, so I would like to query only items 3 and 5 in the following example.
I have got two tables emp_dtl and iou_tab. i have already made entries i.e booking no, emp_cd, emp_name etc in emp_dtl snc its my master table. I want to retrieve the booking nos through lov in iou_tab which are generated in emp_dtl and corresponding info of emp_cd and emp_name should come in the respected fields in iou_tab.
In the above result, We need to go from bottom up and when we hit some value we need to update with the lastest record as below.("Blank" space are considered as null.)
I want to know like How we can select the latest updated record from xyz table. that record has STATUS column. I also want to check if the status is RED or GREEN query should return if the status is red then 1 and if the status is GREEN then it should return 0
I stumbled about some weird 11gR2 behavior (running on AIX).When I performed a join between a table with user based content (parts belonging to an sourcing scope) and a base table (parts available) whereas the parts have to fulfill a special regular expression, it showed that the same query is faster when using outer join than inner join (about 0.7sec vs. 20sec; which makes me believe that regexp_like works wrong when involved in an inner join).
i tried the same statement with a standard like (but not fulfilling the same condition).This time performance was as expected (inner join outperforming outer join).
Oracle version information Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production [code]...
I can see it, the execution plan for the "inner join" doesn't show so much more costs than the one for the outer (but why at all is does an inner join cost more?) ...The execution plan for both "not like" is the same and (surprisingly ;-) ) similar to "outer-regexp".
I hope sample data are not needed as there would be needed a lot...this is the second time I came across the "plan worse but execution time better" phenomenon.
I am trying to join column names from a table with data from a different table. I think i should be able to pass the parameter to a 'select list' in a query. Look at my sample data below. And the data in sales table can grow till 15 rows and similarly corresponding columns in saleshist.
I am having a little trouble retrieving info from my mock database.
here are my definitions: response.setHeader("pragma", "no-cache"); response.setHeader("Cache-control", "no-cache, no-store, must-revalidate"); response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT"); response.addHeader("Cache-Control", "post-check=0, pre-check=0");
[code]...
and here is my select statement. ds = (DataSource) context.lookup(dSource); conn = ds.getConnection(); stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date"); stmt.setString(1, colleagueID); result = stmt.executeQuery();
***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>
1 0 111 Jan-02-07 N 1 1 111 Feb-02-07 N 1 2 111 Mar-02-07 Y 1 3 111 Apr-02-07 Y 1 4 111 May-02-07 Y 1 1 222 Feb-06-07 N 1 1 222 Mar-06-07 N 1 1 222 Jun-06-07 Y 1 1 222 Jul-06-07 Y
The table has incorrect data. meaning for each consumer_id we want the ACTIVE_FLAG to be 'Y' only for it's latest record and the rest to be inactive. i.e. we want the data as follows:
A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.
A_ID, B_ID, CONSUMER_ID, U_DATE, ACTIVE_FLAG.
1 0 111 Jan-02-07 N 1 1 111 Feb-02-07 N 1 2 111 Mar-02-07 N 1 3 111 Apr-02-07 N 1 4 111 May-02-07 Y 1 1 222 Feb-06-07 N 1 1 222 Mar-06-07 N 1 1 222 Jun-06-07 N 1 1 222 Jul-06-07 Y
I have tried using the max function (select part_no, quantity, max(applied) from pcuk_BG_alloc_TAB group by part_no, quantity) but seems as the records have different quantities it treats them separately.
SQL> desc RCA_SMART_CARD; Name Null? Type ----------------------------------------- -------- ---------------------------- N_CARD_ID NOT NULL NUMBER(10) C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20) C_SIM_MSISDN VARCHAR2(20) C_SIM_IMSI VARCHAR2(20) C_LINKED_CARD VARCHAR2(20) N_PRO_IDENTIFIER NOT NULL NUMBER(4) C_CARD_TYPE VARCHAR2(1) N_SIM_STATE NUMBER(1) N_EEPROM_SPACE_LEFT NUMBER(9) N_VOLATILE_SPACE_LEFT NUMBER(9) N_NONVOLATILE_SPACE_LEFT NUMBER(9) N_CARD_OPTI NOT NULL NUMBER(15) N_PRODUCT_ID NUMBER(10) D_CREATION_DATE DATE D_MODIFICATION_DATE DATE D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI; Name Null? Type ----------------------------------------- -------- ---------------------------- MSISDN NOT NULL VARCHAR2(20) IMEI NOT NULL VARCHAR2(16) DATE_MOD NUMBER(13) IMSI VARCHAR2(18) ICCID VARCHAR2(20) T_PROF RAW(20) EXTRA_DATA VARCHAR2(100)
If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
oracle 10g to select column from about 8 table . I start with this statement
select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h from A full outer join B on(A.a=B.b) full outer join C on(B.b=C.c) full outer join D on(C.c=D.d_ . . .. . . .
two history tables with each record having effective date and end date needs to join (date is in dd/mm/yyyy)
table one
effdate enddate ID Name 01/08/2010 04/08/2010 01 devendra 04/08/2010 06/08/2010 01 deven
table two
effdate enddate ID Family 01/08/2010 02/08/2010 01 X 02/08/2010 03/08/2010 01 Y 03/08/2010 05/08/2010 01 Z 05/08/2010 06/08/2010 01 W
Expected output
effdate enddate ID Name Family 01/08/2010 02/08/2010 01 devendra X 02/08/2010 03/08/2010 01 devendra Y 03/08/2010 04/08/2010 01 devendra Z 04/08/2010 05/08/2010 01 deven Z 05/08/2010 06/08/2010 01 deven W
I have a table that contains 15 columns. Say each columns contains name of fruits. And another master table which contains names of valid fruits. Now I have to create another table from these tables such that for each column if the fruit name is present in the master table then fruit-name is populated else "invalid" is populated. I know using left join for 15 times to the same master table can work. But I found this very clumsy. Is there any other way of doing it.This is just a sample case. In real scenario I have a table of 800 million records and master table of 30000 records.
Below is the desc table : SQL> desc RCA_SMART_CARD; Name Null? Type ----------------------------------------- -------- ---------------------------- N_CARD_ID NOT NULL NUMBER(10) C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20) C_SIM_MSISDN VARCHAR2(20) C_SIM_IMSI VARCHAR2(20) C_LINKED_CARD VARCHAR2(20) N_PRO_IDENTIFIER NOT NULL NUMBER(4) C_CARD_TYPE VARCHAR2(1) N_SIM_STATE NUMBER(1) N_EEPROM_SPACE_LEFT NUMBER(9) N_VOLATILE_SPACE_LEFT NUMBER(9) N_NONVOLATILE_SPACE_LEFT NUMBER(9) N_CARD_OPTI NOT NULL NUMBER(15) N_PRODUCT_ID NUMBER(10) D_CREATION_DATE DATE D_MODIFICATION_DATE DATE D_STATUS_MODIFICATION_DATE DATE
SQL> desc IMEI; Name Null? Type ----------------------------------------- -------- ---------------------------- MSISDN NOT NULL VARCHAR2(20) IMEI NOT NULL VARCHAR2(16) DATE_MOD NUMBER(13) IMSI VARCHAR2(18) ICCID VARCHAR2(20) T_PROF RAW(20) EXTRA_DATA VARCHAR2(100)
If I want to join two table together. I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .
Explain the mechanism of this sql statement. I tried to understand how it goes but I really got confused.
select e.last_name, m.last_name mgr from employees e join employees m on (e.manager_id = m.employee_id)
The first line seems clear to me that will bring the names of employees and second column will bring names of managers. Second line is clear as well, but the third line couldn't understand. in my mind it should be like that
m.manager_id = e.employee_id
I tired this but doesn't work so what happened exactly in that full statement, I mean how it will match. I just want to understand the mechanism.
How can we partition a table based on date if it does not have a date column.
Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.
I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.
select distinct c.process_id a.role_id,a.email_address,c.process_name from role a, notification_role b, process c where a.role_id=b.role_id and b.process_id = c.process_id
I have 2 tables as shown below. I have to join those tables and get data as in table 3. Condition is I have to get sum of scores for each student where category is 1 and active is Y.
Table1: col1col2category A 10 1 A 10 2 B 10 1 B 20 2 C 10 1 D 20 1 J 30 1
Table2: colAcolBActive A10Y A20N B30Y B40N Z35Y
Table3: STUDENTSCORE A20 B40 C10 D20 J30 Z35
query to show student name and sum of his score where category is 1 and active is Y. I am using Oracle 8i.
I have 2 tables Table a(girlscoutid, item, quarter)Table b(girlscoutid, fname, lname) I want to get the names of ppl who did not sell any item/s for the
I've created a query so I can easily compare two sets of data for two different instruments:
select a.CalId, a.AtName, a.NRef, a.VaLoat, a.ValTime, a.ValRing, cvs.NRef, cvs.CalId, cvs.AtName, cvs.VaLoat, cvs.Valtime, cvs.ValRing from CalcAttribute a, CalcAttribute cvs where a.NRef like '438815' and cvs.NRef like '438813' and a.CalId *= cvs.CalId and a.AtName *= cvs.AtName union
[Code]...
This works great - however I want to add an addtional condition, basically so it only returns where the two are not equal.
I thought I should just be able to add an extra: and a.ValLoat *<> cvs.ValLoat and a.ValLoat <>* cvs.ValLoat
But it doesnt seem to like this (Incorrect syntax near '<'.)
