SQL & PL/SQL :: Finding Nth Maximum Or Minimum From A Table?
Sep 17, 2011how can i find nth maximum or minimum salary from a employee table? here i want only one row as output.
View 16 Replies
ADVERTISEMENT
SQL & PL/SQL :: Finding Maximum Value Of Two Independent Columns Of Single Table
Jan 18, 2012The challenge I am currently facing is finding the maximum value of two independent columns of a single table. Here is the structure of the table:
[FiscalYear] [AccountingPeriod]
------------ ------------------
[2012] ------------- [2]
[2011] ------------- [12]
[2012] ------------- [1]
What I need to be able to do is to:
1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column Accounting Period.
I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). Is it possible to run this query on Oracle with only one pass through the table where the returned result will show 2012 for FiscalYear, and 2 for Accounting Period?
SQL & PL/SQL :: Finding Record In Table Having Value For Maximum Number Of Columns
Nov 25, 2011if there is any inbuilt function or way to find a row in the table that is having value for maximum number of columns.
For example, the table A has 5 columns
(c1,c2,c3,c4,c5) and it has 3 records(r1,r2,r3)
and
r1 has values only for c1,c2
r2 has values only for c1,c2,c3,c4
r3 has values only for c1
so I should get the result as "r3 has values for 4 columns & it is not having value for column c5".
PL/SQL :: Finding The Maximum Truncate (date)
Jul 31, 2012I've the following data set
with t as
(
select 1 cor_id , 'SR' rt_cd,TO_Date( '05/12/2010 01:12:19 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 2 cor_id , 'SL' rt_cd,TO_Date( '05/12/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 3 cor_id , 'SR' rt_cd,TO_Date( '04/10/2010 02:11:15 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 4 cor_id , 'SL' rt_cd,TO_Date( '04/10/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual
)
select t1.*
from t t1for each iss_id =100 ,
there could me more than one record in the above data set, The requirement is i need to get the records where the trunc(dt) is maximum
I need to get 2 records with cor_id= 1 and 2.
how can i get those records
Semantic Technologies :: Maximum Node ID Larger Than Java Maximum Integer Value
Mar 7, 2013I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:
-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....
when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.
Did I miss something when I created my network?
Finding Primary Key In A Table?
Oct 4, 2007Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies View RelatedSQL & PL/SQL :: Maximum Length Of Table Name
Jun 10, 2013I want to create a table with a length greater than 30.I Thought there was a way to override the max length for for a table name in Oracle 11.2.0.2.I cant find a documentation that states how to get it done.
View 1 Replies View RelatedMaximum Length Of Table Name?
Jan 31, 2013I think the maximum length of table and column name in oracle 11g is 30 characters.I want to increase the limit as i want to import a mysql database that is having bigger table names.Can i preset the table name and column name length??
View 2 Replies View RelatedSQL & PL/SQL :: Finding Percentage Using Query From Table Data
Jun 23, 2010SL_NoStatus
191502Implemented
191690Implemented
190998Implemented
191346Implemented
190541Implemented
[Code]..
my above table consists of two columnc sl_no and Status,col1 indicates the process no and the status indicates it is implemented or cancelled or failed during implementation.
i need to find the percentage of the implemented+cancelled process over failed..
test case:
lets consider,
A->count(Sl_NO)
B->Count(STATUS) where STATUS='Implemented'
[Code]..
i think i have satisfactorily given enough data.. make it out using sql query..
SQL & PL/SQL :: Finding Out If A Table Column Has Special Characters
Jul 15, 2010I have this table column of varchar2 data type. I need to find out if it contains anything other than numbers or alphabets(basically to see if it contains any special characters in it).
I just need to fetch only those rows that has special characters. How do I get this?
CREATE TABLE CHECK_SPL_CHARAC
(ADDRESS VARCHAR2(100));
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC%ABC');
INSERT INTO CHECK_SPL_CHARACVALUES ('ABC_ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC?ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC*ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('1234');
INSERT INTO CHECK_SPL_CHARAC VALUES ('ABC');
INSERT INTO CHECK_SPL_CHARAC VALUES ('AB C');
INSERT INTO CHECK_SPL_CHARAC VALUES ('12 34');
COMMIT;
I need to get the first 4 rows alone in the sql output. I do not wan to get the last 2 rows with space.
SQL & PL/SQL :: Finding Child And Parent Rows In Table?
Jun 6, 2011I have table emp that contains empno, empname, mgr .what i want is a general procedure that will take empno as input and will give all the child rows and parent for entered empno.
for ex
E
A-->B-->C-->D
F-->G
H
When i will pass d as node it will return c,b,a,e,f,g,h
SQL & PL/SQL :: Oracle Table - Finding Transaction Logs?
Dec 9, 2010How can we see the history of transactions on a table.
For eg, If I have a table called TEST123, I would like to know whatever SQLs fired on that table on today.
SQL & PL/SQL :: Table Column Name - Maximum Length
Apr 8, 2011What is the Maximum Charter length can be given as a column name in a table?
View 3 Replies View RelatedSQL & PL/SQL :: Finding User Who Made Changes In Table Structure Or Schema
Sep 21, 2011i want to find the name of user who make changes in the table structure or create any index or constraint or unique key or alter the column? Is there any way to find in Oracle. in which table what change has been done as well?
following Output needed
userid, username, schemaname, schemachangetime, "what_change_has_been_made", IP address or Computername
SQL & PL/SQL :: Finding Next Record In Cyclic Order In Table Using Sequence
Sep 4, 2010I have a table as below:-
CREATE TABLE TEST_EMP_WIP
(
EMP VARCHAR2(20 BYTE),
WIP NUMBER,
MAX_SEQUENCE NUMBER
)
[Code]....
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 4
011 1 5
I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.
select emp_next from (
SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next
from test_emp_wip where wip > 0 )
where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0)
EMP_NEXT
010
After sometime, the record would look like :-
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 6 4
011 1 7 5
Now the next update should be done to the EMP '002' in cyclic manner.
tell me a way to do this. I can insert new columns if needed in table for calculation.
SQL & PL/SQL :: Find Maximum Iteration In Table And Change Data
Jul 27, 2011i have inner query result like
Emp status
----- ----------
ram a
ram a
ram b
ram a
i want write a query to find a maximum occurrence of status and update in status column.
result should be
Emp status
----- ----------
ram a
ram a
ram a
ram a
i tried lot of thing but not to go.
PL/SQL :: Create_Date Must Be Set To Minimum Of All?
Jun 29, 2013I have a table structure as follows Student(Id,First_Name, Last_Name, email, Contact, Address1 ,Address2, City, Edit_Date,Create_Date,Archived)
Now if there is more than one row with same email the one with the latest edit date should be updated with missing fields by using same field value other rows (if the field is present in more than one row, the one with the next latest edit date is to be considered) and
the archived status of all rows with same email except this master row must be set to 1.The Create_Date must be set to the minimum of all the create_date values of rows with same email value
Return Minimum Value Of Resultset
Jul 22, 2010SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.
SQL & PL/SQL :: Getting Minimum Composite Key From Group By
Oct 11, 2011I have the following case:
create table try_o
(pk1 NUMBER, pk2 NUMBER, fld1 VARCHAR2(10), fld2 NUMBER,
PRIMARY KEY (pk1, pk2));
insert ALL
into try_o values (1,1,'f1',5)
into try_o values (1,2,'f1',5)
[Code]....
I need to get the row that contains the least pk2 among the group of records with the same fld1 and fld2. but if I used
select min(pk1), min(pk2), fld1, fld2
from TRY_o
group by fld1, fld2;
I get a record that doesn't exists as the min(pk1) and min(pk2) are from different records. I need to get the min(pk1) and its corresponding pk2.
Minimum Size For Db_recovery_file_dest_size
Apr 25, 2013i am trying to enable flashback on in my database and i am getting the below error.
idle> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38708: not enough space for first flashback database log file
my db_recovery_file_dest_size is 1g. If i change it to 20g i am able to "alter database flashback on".
Why does a size of 1g give this proble? Any simple steps or recommendation that i am missing? Or is the size of the DB which impacts the db_recovery_file_dest_size also?
idle> select *from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
ODP.NET :: Minimum Role For GetSchema
Apr 16, 2013I am trying to find out what is the minimum Oracle User Role required for the GetSchema command to work using ODP.NET and the Oracle.DataAccess library.
Dim cn As New OracleConnection(ConnectionString) 'Oracle.DataAccess.Client.OracleConnection Return cn.GetSchema("Tables")
Return Minimum Duplicate Values
Dec 16, 2009I am trying write a script that will return all values (based on the minimum tarif) from the Germany table for any duplicate values. Duplicate values are any values with the same UFI, ZC,limitid,depot. The German table also contains the fields tarif, city, supplier, etc.
Below is the script I have previously used to sort out duplicates. I have tried 50 different ways get it to return just lines for the minimum tariff but haven't been successful.
select *
from Germany t
where (ufi,zc,limitid,depot) in (
select ufi,zc,limitid,depot from (
select ufi,zc,limitid,depot, count(*) n
from Germany t
group by ufi,zc,limitid,depot)
where n<>1
)
SQL & PL/SQL :: Extract Minimum Report Time
Oct 1, 2013I have the below scenario:
[u]Report_Time M I Ta[/u]
02-SEP-13X Y35167005
01-SEP-13X Y35931902
03-SEP-13X Y35931901
The output I am expecting from above is:
X Y 01-SEP-13 03-SEP-2013 35931902 35931901
II need to extract minimum report time , max report time and corresponding Ta.
SQL & PL/SQL :: How To Find Minimum Salary Holders
Nov 7, 2012in this query, i am stuck in this little query but cant get answer lets suppose
select * from emp;
EMPNO ENAME JOB SAL
---------------------- ---------- --------- ----------------------
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3000
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
7900 JAMES CLERK 950
[Code]....
now if i want to see min salary takers group by job then i use
select x.job, min(x.sal), count(*) from emp x group by x.job;
JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 5
CLERK 950 3
PRESIDENT 5000 1
MANAGER 2450 5
Developer 2975 1
ANALYST 3000 2
The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.
My question is how can i get number of person on min salary?
Possibly my data should be like as this
JOB MIN(X.SAL) COUNT(*)
--------- ---------------------- ----------------------
CLERK1 800 1
SALESMAN 1000 1
CLERK 950 1
PRESIDENT 5000 1
MANAGER 2450 3
Developer 2975 1
ANALYST 3000 2
Reports & Discoverer :: Select Query With Minimum Values
May 30, 2013The prob is i want to display minimum intime and max outtime in idate against employee,report keep displaying multi inout records of an employees!
SELECT div.division,
DEP.department,
E.employeecode,
E.name empname,
DES.designation,
i.idate,
To_char (Min(i.intime), 'HH:MI:SS AM'),
To_char (Max(I.outtime), 'HH:MI:SS AM'),
Round(i.btime / 60),
e.shift
[code]....
Backup & Recovery :: Minimum Files Required To Open DB
Dec 26, 2012what are the minimum files which are required to open the db ? take it as this
I have oracle installed in c drive. I have placed 3 datafile related to one tablespace say test: two in D and one in E. I have place 3 datafile related to tablespace say orcl : two in D and one in E.
my undo is in E scenario is that my E drive crashed . db is in no archive mode . no backup is there. to be more worst db shuts down abnormally. I want my db open with data of c drive and D drive.
SQL & PL/SQL :: Distinct And Individual Records In Single Or Minimum Pass(es)
Feb 21, 2012I have following data
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPE
------------------ ------------------------------ ------------------
AAA/wuAAHAAAW73AAA CON$ TABLE
AAA/wuAAHAAAW73AAB I_COL2 INDEX
AAA/wuAAHAAAW73AAC I_USER# INDEX
AAA/wuAAHAAAW73AAD C_TS# CLUSTER
AAA/wuAAHAAAW73AAE I_OBJ# INDEX
AAA/wuAAHAAAW73AAF I_CON2 INDEX
6 rows selected.
I want it in the following manner
select rowid,object_name,object_type from do;
ROWID OBJECT_NAME OBJECT_TYPEGROUP
------------------ ------------------------------ ---------------------------
AAA/wuAAHAAAW73AAA CON$ TABLE2
AAA/wuAAHAAAW73AAB I_COL2 INDEX1
AAA/wuAAHAAAW73AAC I_USER# INDEX1
AAA/wuAAHAAAW73AAD C_TS# CLUSTER1
AAA/wuAAHAAAW73AAE I_OBJ# INDEX1
AAA/wuAAHAAAW73AAF I_CON2 INDEX1
6 rows selected.
Here the GROUP is changing when the data type is changing and thus for same data type the group shall remain the same As of now this is achieved by - first selecting distinct object_type, then it's mod(rownum,<input variable) and this result of 'mod' is doing the grouping which is the retrieved along with rowid of all individual record
Present query is as following and it is not much efficient
SELECT DO.ROWID RWID, RID
FROM DO,
(
SELECT OT,CASE MOD(ROWNUM,:v) WHEN 0 THEN :v ELSE MOD(ROWNUM,:v) end as RID
FROM(
(SELECT DISTINCT OBJECT_TYPE OT
[code]....
I tried using sequence with cycle but it gave different results. Even I tried following but it did not gave satisfactory results
select d.rowid,d.object_type,x.x1 from do d,(select distinct object_type,mod(rownum,:v) x1 from do where
created>'01-jan-2008')x where d.object_type=x.object_type and created>'01-jan-2008';
In short the query needs Distinct with mod(rownum) and individual records in a single pass The mod(rownum) i.e. group shall change when the object_type changes but then shall remain constant through out the particular object_type.
Minimum Extent Error Creating Tables In Empty Tablespaces
Dec 30, 2010I am trying to restore to a backup instance on a backup server. When I try to recreate the tables I keep getting ORA-01659: unable to allocate MINEXTENTS. The tablespaces and datafiles on both servers show as the same size in OEM.
I have dropped all tables and OEM shows tablespaces are empty. Then I run a script to recreate all tables. Most of the tables don't get created because their TS is full. After the script to recreate all tables runs, the main tablespaces are full, more full than on the production machine. I have also tried ALTER TABLESPACE xxx COALESCE; on each tablespace right after dropping all tables and before recreating them to reclaim free space. Why is it full? I've only dropped and created the tables, there shouldn't be any data in them yet.
ORA-01659: unable to allocate MINEXTENTS beyond 2 in tablespace PLUS_T...The backup instance was already there, all I did was drop the tables. Here's what I ran on prod to build a script to recreate the tables on backup server. Got it off Burleson somewhere.
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) ||'; '
FROM USER_TABLES u;
Server Administration :: Minimum Datafile Size Required For Creating Non-system Tablespace?
May 21, 2007what is the Minimum datafile size required for creating a non-system tablespace?
I am trying to create a tablespace by giving the syntax like,
SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 72k;
create tablespace t1
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace t1
2 datafile '/home/data/t1.dbf' size 73k;
Tablespace created.
The blocksize for my database is 4096, as i have heard that the minimum size of the datafile is decided by blocksize, but i want to know that how it is calculated as by giving the above syntax the other values will be default. I am trying the syntax in oracle 9.2.0.1.0 version.
PL/SQL :: Finding The Max Value
Jun 19, 2013I have the following query where i need to find the max value[code] with t as
( select 'L1' R_nm ,'Data' R_Data , 'Obj' R_Obj , 'Wd' r_prec , '2' val
from dual union all select 'L1' , 'Data', 'Obj' , 'No', '4'
from dualunion all select 'L2' , 'Data', 'Obj' , 'No', '4' from dual )
select t.*, max(val) over(partition by r_nm,r_data,r_obj) rk from t[/code]
My expected output should be
[code]
r_nm r_data r_obj r_prec val rkL1 Data Obj Wd 2 1L1 Data Obj No 4 2L2 Data Obj Yes 1 1
[/code]