Selecting The Most Recent Value?

Sep 7, 2007

Have a table like this:

ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360

I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:

1246/1/2006410
2247/1/2007360

I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.

View 3 Replies


ADVERTISEMENT

Finding 10 Most Recent Missions

Jun 23, 2008

What i have is table with:

mission_id, mission_type_id, security_level and code_name.

What i have to do is get the 10 most recent missions and change their security level to the highest one in their mission_type_id but ONLY if code_name length is >7

So far I have this, the problem is that the oracle moans about the order and wants me to close the bracket before the order

sql

UPDATE missions m
SET m.security_level = (
SELECT max(m2.security_level)
FROM missions m2
WHERE m2.mission_type_id = m.mission_type_id
AND length(m2.code_name) > 7
)
WHERE m.mission_ID IN (
Select m3.mission_id
From missions m3
ORDER BY m3.mission_id desc)

View 3 Replies View Related

To Select Recent Row Updated Detail

May 26, 2011

I have one table,in that table have number of rows. I want to select recent row updated details.

View 2 Replies View Related

PL/SQL :: Get Not Null Recent Values From History Data?

Jul 5, 2012

Below given is the sample data

SELECT *
FROM
(
SELECT 1 ORDR_ID, TO_DATE('01-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 5 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('15-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'KPD' Colr_CD FROM DUAL UNION ALL
SELECT 1 ORDR_ID, TO_DATE('16-JAN-2012', 'DD-MON-YYYY') INWD_DATE, NULL SIZE_, 'ALD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('02-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 9 SIZE_, 'APD' Colr_CD FROM DUAL UNION ALL
SELECT 2 ORDR_ID, TO_DATE('05-JAN-2012', 'DD-MON-YYYY') INWD_DATE, 10 SIZE_, '' Colr_CD FROM DUAL UNION ALL

[code]....

Where every first row is the initial entry for an order and following rows are the changes done to the order with modification date

And the expected output is

ORDR_ID     Start_date     End_date     SIZE_     COLR_CD
1     01-01-12     01/15/2012     5     APD
1     01/15/2012     01/16/2012     5     KPD
1     01/16/2012     00-01-00     5     ALD
2     01-02-12     01-05-12     9     APD
2     01-05-12     01-06-12     10     APD

[code]....

I cam use a select statement within select, but wanted to write an efficient SQL...

I'm using

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0

View 2 Replies View Related

ORA-00338 - Log 3 Of Thread 1 Is More Recent Than Control File

May 9, 2013

I encountered error ORA-00338 while performing incomplete database recovery:

ORA-00283: recovery session canceled due to errors
ORA-00338: log 3 of thread 1 is more recent than control file
ORA-00312: online log 3 thread 1: '+DG_REDO/dbase/onlinelog/group_3.267.814820795'
Recovery Manager complete.

I performed this recovery on oracle 10g, ASM database.

1. What are possible causes behind this error ORA-00338 ?

2. This error occurs at the start of media recovery, while RMAN is applying archived logs. So does RMAN apply "online REDO" logs after applying "archived" logs required for recovery or before them ?

3. How does rman come to know that online log 3 is more recent than control file ? How can I get scn of online log & control file in my environment ?

Following are few details related to checkpoint scn/change# info. I found in my environment..Before recovery:

view
v$datafile: 642935
v$datafile_header: 642935
v$database: 642935
v$log: 640956

After unsuccessful recovery (ORA 338 :( )
v$datafile: 640917
v$datafile_header: 640056
v$database: 640906
v$log: 640906

View 26 Replies View Related

SQL & PL/SQL :: Update Complete Flag Most Recent 2 Rows By Category

Jul 29, 2013

I would like to update the complete flag of all the rows of a table except the most recent two rows of each category.

The table has fields like:
category - string,
item - string,
creation_date - string ('YYYYMMDDHHMI'),
complete_flag - boolean,
etc.

Each category does not have the same amount of records with the same creation_date so I do not want to filter by creation_date. Is there a way to accomplish this?

View 8 Replies View Related

SQL & PL/SQL :: Return Most Recent Record Previous To Start Date?

Nov 18, 2011

I have a sub query (already dervived from several other tables) that has a list of children in with the date they started their course (tableofchildren). Child IDs may be duplicated in this query but each record will have unique start dates per child ID.

I have a second sub query which lists workers involved with the children in the first query (tableofworkers). A worker may be responsible for more than one child but has the unique child in the record to identify involvement with the child.

I need to join these queries together to return the child's record with the WorkerName and the AllocatedStartDate of the worker who was most recently involved with the child prior to the date (EnteredCourseDate) the child started their course (OutputWanted) - the worker associated with the child when they started their course.

A couple of points - I need to deploy this into another reporting application that doesn't support cursors etc or the 'With' operator. Also, I tend to join tables/queries in the Where clause so if it's possible that way that would be great.

OC.
create table tableofchildren
(ChildID varchar(20),
ChildName varchar (50),

[Code].....

View 13 Replies View Related

Restore Recent Backed Up Control File And Then Restore The Database?

May 6, 2013

The controlfiles were corrupted due to some issue. So, I had to restore the recent backed up control file and then restore the database.

The weird thing which I have noticed is that. I tried backing up Yesterday's Control file. So, I would say that the backup pieces it should pick should be from yesterday. however, the backup pieces are getting restored from last week's backup.

View 3 Replies View Related

SQL & PL/SQL :: Selecting 6th Top Value

May 9, 2010

how to select the sixth highest earner in my employees table.how to select 6th lowest earner..

View 18 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.

CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)

[code]....

View 17 Replies View Related

PL/SQL :: Selecting Data For One Day

Jun 18, 2013

I want to select data inserted in the table for that day only.

Table name -->ADJCOLUMNS

i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.

select  account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1)  - 1/86400,'DD-MON-YY hh24:mi:ss');

View 4 Replies View Related

SQL & PL/SQL :: Selecting 1st And 2nd TOP Numbers (from Same Row)

Jul 6, 2012

I need to find the top two values value for each ID Number:

CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));

INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');

Desired Result:

ID Number 1st 2nd
12345679 30 30
99999999 30 25

View 7 Replies View Related

PL/SQL :: Selecting Into A Varray?

Mar 29, 2013

BANNER
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE     11.2.0.3.0     Production
TNS for HPUX: Version 11.2.0.3.0 - Production

[code]...

SELECT job_request_id,
         CAST (COLLECT (USER_ID) AS SYS.OdcinumberList) user_ids
    FROM mytable
GROUP BY job_request_id;

ORA-22814: attribute or element value is larger than specified in type

View 6 Replies View Related

Selecting From Two Tables - How To Get Value From Session

Jan 31, 2013

I have tables SUBJECT(subject_id, name, number) and PS(ps_id, subject_id, student_id). I need to select all from SUBJECT,subject_id and student_id from SP, joined by subject_id, where student_id needs to be read from session. I'm using asp.net with oracle database. How to get the value from the session.

View 2 Replies View Related

Selecting 100 Number Rows?

Jan 15, 2011

how can I select whole table in parts of 100 rows?

If I have primary key I can:

CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;

How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?

View 1 Replies View Related

Selecting Via Multiple Tables

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]...

What I get is in green (services are calculated more than 2 times BUT in the same report).

What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]...

One person (zmo_kodas) can have a lot of reports (ats_nr).

Every report can have one or more services (pas_kodas).

View 1 Replies View Related

SQL & PL/SQL :: Selecting Clob Value Over DBLink?

Sep 10, 2012

any way to select the clob value from dblink.?

View 7 Replies View Related

SQL & PL/SQL :: Selecting Only Part Of A String

Feb 25, 2010

I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Selecting First Row From Multiple Joins?

Apr 2, 2010

I'm putting together a path to select a revision of a particular novel:

SELECT e.documentname, e.Revision, e.VersionNumber
FROM Catalog, BookInCatalog
INNER JOIN NovelMaster
INNER JOIN HasNovelRevision
INNER JOIN NovelRevision e
LEFT JOIN NovelRevision s

[code]...

My goal here is to select the earliest revision from the set of Novel revision. The revision field is a string.

When I run the query for Novels that have multiple revisions I get multiple records. If there is just one record I only get one row. If there are two I get four (two for each revision). As the number of revision increases it looks like it just mushrooms from there.

One other challenge is the format of the revision- a revision sequence could look like this:

A
B
C1
C2
C
D
E1
E

So there are "intermediate" revision referred to by a number. In this case I would select revision A, but if I had:

A1
A
B1
B

I would want to select B. I am pretty sure that all the revision are stored in the db in order.Notice that the comparison operator ">" is used in e.Revision > s.Revision. I initially though it should have been "<" because we want to select the initial but the other way gives me the right order (though the wrong results).

View 12 Replies View Related

SQL & PL/SQL :: Selecting Via Multiple Tables?

Jan 19, 2011

I need to calculate a list of people, who got some services more that 2 times with the same service koda (pas_kodas) to the same person (zmo_kodas). It should not depend on report number.

[URL]

What I get is in green (services are calculated more than 2 times BUT in the same report).
What I need is in red: calculate servises more that 2 times ACCROSS all reports to the same person (zmo_kodas).

[URL]

One person (zmo_kodas) can have a lot of reports (ats_nr). Every report can have one or more services (pas_kodas).

View 2 Replies View Related

PL/SQL :: Selecting Table Name Under A Variable

Mar 21, 2013

correct this one.

declare
v_ename varchar2(10):='emp';
begin
for j in (select ename from v_ename)
loop
dbms_output.put_line(j.ename);
end loop;
end;

getting error v_ename table does not exists. i should use v_ename, as i dont know the table name.

View 4 Replies View Related

SQL & PL/SQL :: Selecting Latest Date

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

SQL & PL/SQL :: UNION While Selecting From Dual?

Sep 19, 2013

I would like to SELECT these 3 hardcoded titles from DUAL, and have a blank line under each, on the output in this order from the SQL. But the result does not end up that way

SQL> set heading off;
1 select '#ENCODING WINDOWS-1252' from dual
2 union
3 select ' ' from dual
4 union
5 select 'Language Section EN-US' from dual
6 union
7 select ' ' from dual
8 union
9* select 'Catalog Section Title Date Source' from dual
SQL> /

#ENCODING WINDOWS-1252
Catalog Section Title Date Source
Language Section EN-US

- - - - - - - - - - - - - - - - -

Desired Output:
#ENCODING WINDOWS-1252
Language Section EN-US
Catalog Section Title Date Source

View 12 Replies View Related

Selecting A Set Of Records From One Table

Aug 19, 2010

I'm selecting a set of records from one table, for example: ID, description and date. From this I'm only wanting the latest inserted row. I've used the max function on the date which is fine, however, there are some records that have had their description changed. This then returns two values for one ID, the max for the original description and the max for the changed description.

I'm getting:

ID |Description |Date
1 ABC 01/01/2010
2 XYZ 02/03/2010
2 XYZ1 03/05/2010

When I want:

ID |Description |Date
1 ABC 01/01/2010
2 XYZ1 03/05/2010

As ID 2 with XYZ1 Description is the very latest row for that ID.

This is an audit table so the ID appears on numerous rows as it a composite key with date.

View 1 Replies View Related

Selecting From Column Using RowID In Table?

Apr 24, 2012

I just want to know that "is it safe to select a columns using ROWID in a table?"

View 3 Replies View Related

Selecting All Queries Executed Between Given Date And Now

Oct 26, 2010

Is information in v$sql enough to select all queries executed between given date and now? When the queries are removed from v$sql?

View 1 Replies View Related

SQL & PL/SQL :: Selecting Data Greater Than Some Value Into One Column

Jul 15, 2013

I have table TEST_REP with below data

DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23

13 rows selected

I have used following query to get the below output:

select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......

Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0

10 rows selected

Expected Output :

MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46

View 6 Replies View Related

SQL & PL/SQL :: Selecting Owner For Currently Called Object?

Sep 13, 2010

I am trying to select the owner of a certain object, only knowing the name of the object and the user calling it.

Problem is this object might exist in more than one schema with the same name, and I only need the one that is called by the active user, which himself/herself might have access to other schemas containing their own copy of the object.

Example:

SELECT OWNER
INTO v_schema_name
FROM ALL_OBJECTS
WHERE OBJECT_NAME = p_object_name;

This works fine, until the object exists in more than one schema to which the current user has access.

View 4 Replies View Related

SQL & PL/SQL :: Selecting Correct Record From The Table

Sep 26, 2013

I have a table with following structure:

CREATE TABLE ID_comments
(
ID CHAR(10 BYTE) NOT NULL,
S_COMMENTS VARCHAR2(255 BYTE),
P_COMMENTS VARCHAR2(255 BYTE),
C_COMMENTS VARCHAR2(255 BYTE)
);

For each Id, I can have multiple records.

Below is the insert script of one of the ID:

Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 single','');
Insert into ID_comments values ('0813654254','','JR/0813653606 SINGLE','');
Insert into ID_comments values ('0813654254','','JR','');
[code].......

Now I want to select only one record from this table for an ID, which will have "not null" values for s_comments,p_comments,c_comments columns. If for some ID , there is no "not null" row for any column, then pick up the "null" row/value for that column.

View 14 Replies View Related

Forms :: Record Selecting In LOV Shows?

Jul 5, 2010

i created a form by using wizard...i want to improve that form...

I created one lov which is attached in one of form field..now when i press f9 lov displayed..

But when i select any one record in that LOV , its does not come in my form field

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved