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
View 3 Replies
ADVERTISEMENT
Apr 19, 2010
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 cannot create the indexes on this table.
View 11 Replies
View Related
Apr 3, 2012
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.
drop table a;
create table a(
seq number,
custom_id number,
repair_id number,
repair_date date);
[code]........
View 10 Replies
View Related
May 21, 2011
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.
View 1 Replies
View Related
Mar 17, 2011
DROP TABLE TESTING CASCADE CONSTRAINTS;
CREATE TABLE TESTING
(
DATAPERIOD DATE,
EMPLID VARCHAR2(20 BYTE),
B_OS_REASON VARCHAR2(9 BYTE)
)
TABLESPACE USERS;
[Code] ........
SQL> select * from testing order by 1;
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972
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.)
DATAPERIO EMPLID B_OS_REAS
--------- -------------------- ---------
01-OCT-10 2387972
01-NOV-10 2387972
01-DEC-10 2387972 XXXXXX
01-JAN-11 2387972
01-FEB-11 2387972 XXXXXX
View 5 Replies
View Related
Jul 2, 2012
How to export latest n records from a table using the below query(latest n records)
select * from (select empid,ename,sal,joining_date from emp order by joining_date desc) where rownum<n;
View 3 Replies
View Related
Mar 21, 2013
i have requirement, that is to retrieve the data from pl/sql table.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
[code]....
PL/SQL procedure successfully completed.now i want receive the data from particular x record to y record
View 8 Replies
View Related
Sep 21, 2011
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
View 8 Replies
View Related
Jul 30, 2012
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.
View 10 Replies
View Related
Jul 18, 2012
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.
CREATE TABLE SALESHIST
(
PRODUCT VARCHAR2(30 BYTE),
Q1 VARCHAR2(30),
Q2 VARCHAR2(30),
Q3 VARCHAR2(30),
Q4 VARCHAR2(30)
)
[code]......
View 6 Replies
View Related
Feb 21, 2011
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%>
View 5 Replies
View Related
Feb 8, 2007
The oracle table has the following fields,
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 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
View 14 Replies
View Related
Aug 9, 2013
I have a query where i need to pull back the latest dated record for a tariff
my data looks like
Col_A Col_B Col_C
1| 100 29-Sep-11 Tariff_1
2| 200 24-Apr-12 Tariff_2
3| 300 17-Oct-12 Tariff_3
and i need to add a subquery which pull back the col_c with the larges Col_B i.e i need to pull back tariff_3 only
i tried using a max but dont think that will work.
...
and c.Col_c in ( select col_c where max(col_b))
View 21 Replies
View Related
Jul 31, 2010
SQL> select * from emp;
SINO BOOK UPDATION_
---------- ---------- ---------
1 UB 01-MAR-10
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 12-JUN-10
4 MB 31-JUL-10
6 rows selected.
SQL> SELECT sino, book, updation_date
2 FROM emp
3 WHERE updation_date IN (SELECT MAX (updation_date)
4 FROM emp
5 GROUP BY book);
SINO BOOK UPDATION_
---------- ---------- ---------
2 UB 12-MAR-10
3 SB 12-MAR-10
4 DB 12-MAR-10
4 MB 31-JUL-10
I would like to know, how to find out the latest date from above query without using group functions like max, min,order by and group by.
View 10 Replies
View Related
Aug 23, 2012
have a bit of a SQL trouble. I have a simple table (pcuk_BG_alloc_TAB) which stores Parts, Quantities and Applied dates
PART_NO QUANTITY APPLIED
PartA 100 10/8/2012
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 50 10/8/2012
PartC 75 15/8/2012
PartC 80 21/8/2012
I am only interested the latest applied date for each part and am looking for this to be returned in a select statement (as below)
PART_NO QUANTITY APPLIED
PartA 200 12/8/2012
PartB 30 12/8/2012
PartC 80 21/8/2012
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.
View 21 Replies
View Related
Aug 9, 2012
I want to get rows by latest date for each group pf rows.
say my data is :
TYPE1 TYPE2 quantity DATE-ORDERED
sweets chocolate 10 05-FEB-2012
sweets chocolate 10 04-DEC-2012
sweets chocolate 10 08-FEB-2012
pastries chocolate 20 08-AUG-2012
[Code] ..........
I want to get results by latest date,
sweets chocolate 10 04-DEC-2012
pastries chocolate 20 08-AUG-2012
sweets vanilla 10 05-DEC-2012
pastries vanilla 20 05-NOV-2012
I have tried Queries with Max(DATE-ORDERED) and grouping it..its not showing me results because I don't have indexes in my data.
View 2 Replies
View Related
Mar 13, 2012
I need a query to get data for the latest date for some purchases.
My expected output would be
Date - Item - Price - Qty
10/6/2011 10129 .5 1
6/8/2011 10130 13.33 9
2/6/2011 10131 21.74 24
Below are the scripts for this.I have also attached it.
CREATE TABLE XX_PO_LINES_ALL
( CREATION_DATE DATE,
ITEM_ID NUMBER,
UNIT_PRICE NUMBER,
QUANTITY NUMBER )
[code]......
View 4 Replies
View Related
Nov 1, 2010
I have two table in different instance .
IMEI in instance A
RCA_SMART_CARD in instance B
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 .
View 1 Replies
View Related
May 17, 2010
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_
.
.
..
.
.
.
View 12 Replies
View Related
Aug 16, 2010
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
what can be optimum sql for this?
View 3 Replies
View Related
Nov 10, 2010
I have the following 2 tables.
A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);
[Code]..
Join column : object_id and parent_id.
The object id in docversion will have multiple values for element value in listofstring table.
ordinal value represents teh sequence of element value
Eg:
data for docversion:
1 23
2 34
data for LISTOFSTRING:
1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B
ouptut:
I want the output as follows
docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B
View 14 Replies
View Related
Apr 12, 2010
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.
View 7 Replies
View Related
Mar 18, 2011
I have create a select statment using more then 10 table which is returning only two columns as per my requirement.
From this select statement I have create a View?
In order to proceed further first I would like to as If I can join a table and a view?
View 3 Replies
View Related
Nov 1, 2010
I have two table in different instance .
IMEI in instance A
RCA_SMART_CARD in instance B
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 .
How can I do and what is the sql statment ??
View 5 Replies
View Related
Jun 11, 2010
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.
View 4 Replies
View Related
Jun 21, 2012
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.
View 1 Replies
View Related
Apr 28, 2010
I am little confused applying DISTINCT on a Multiple table Inner join.
Table: Role
=========================
role_id email
1 xxx@abc.com
2 yyy@abc.com
3 zzz@abc.com
Table: notification_role
===========================
id role_id process_id
1 1 p1
2 1 p2
3 1 p3
4 1 p1
5 2 p2
6 2 p2
7 2 p3
8 2 p3
9 3 p4
Table: process
================
process_id proces_name
p1 process1
p2 process2
p3 process3
p4 process4
Expected Result
====================
role.role_id role_email process_process_id process_name
1 xxx@abc.com p1 process1
1 xxx@abc.com p2 process2
1 xxx@abc.com p3 process3
2 yyy@abc.com p2 process2
2 yyy@abc.com p3 process3
3 zzz@abc.com p4 process4
QUERY::
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
View 3 Replies
View Related
Mar 1, 2010
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.
View 8 Replies
View Related
Nov 2, 2013
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
4th quarter
girlscoutid item quarter7771
drinks 4QS9000
tickets 4QW7771
cookies 2QS5085books 3QF3010tickets3QW5085
cookies1QF lscoutid
fnamelname7771 jenn richochet9000 laura wilkins5085sally mae3010dora mckenzie
This is what I have, however, I get duplicates and also 7771 shows up on the list even though she sold an item on the 4Q.
SELECT fname, lname, a.girlscoutid
FROM a,b
WHERE a.girlscoutid = b.girlscoutidAND term NOT LIKE '4Q_';
View 4 Replies
View Related
Aug 20, 2010
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 '<'.)
View 3 Replies
View Related