Select Row With Highest Version
Aug 25, 2010
My table holds info on documents:
|doc_id|auth|version|date_created|
so what I'm trying to do is select the latest version of each document record.
so say I have there records:
doc_uid|doc_id|auth|version|date_created|
|1 | 100 |al |0.1 |11/4/08 |
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
So i want to select only one copy of every doc_uid(unique id) with the highest version number, which would make my output:
doc_uid|doc_id|auth|version|date_created|
|1 | 101 |al |0.2 |11/4/08 |
|2 | 102 |al |0.4 |11/4/08 |
doc_id 100 was excluded because it has a higher version which was shown instead.
So how would i express this is oracle sql.
edit: Just thought i should say that I have been tring to crack this for 2 days now :P I have been messing around with MAX() but I cant make it do what i want.
View 6 Replies
ADVERTISEMENT
Oct 15, 2010
We are planning to upgrade a database from 9.2.0.8.0 to 10.2.0.4.0.We have a lot a PRO*C programs pre compiled using 9.2.0.8.0 (and most likely even 9.2.0.7.0) compiler.
if we could upgrade the database without having to re-compile all the programs.We have tested this approach against some of our programs. Most of them executed fine - but in 2 cases we are getting "ORA-01001 invalid cursor". I suspect, that the case is that Pre compiler version 9 is not supported against 10g databases - but I am not sure.
Would it be a better option to upgrade Pre compiler/client as the first step and the db as the second step (and would that be supported) ? We definitely don't want to upgrade both Pre compiler/Client and the Database in one goal - that would be too risky.
View 3 Replies
View Related
Jun 3, 2010
I have a report of version 10g . I want to run this report in oracle reports 6i version.
View 1 Replies
View Related
Feb 24, 2010
I have an table named as rawdata.I need output comes
F_DATE MAGNITUDE
--------- ----------
11-JAN-10 9
create table rawdata(f_date date,magnitude number);
select * from rawdata;
F_DATE MAGNITUDE
--------- ----------
10-JUN-09 6
10-DEC-09 6
11-JAN-10 7
11-JAN-10 8
11-JAN-10 9
View 5 Replies
View Related
Mar 30, 2012
I am using Oracle 11g version, I wanted to check if a particular function is available in the earlier version of Oracle (Say 9i). Oracle optimizer to run the query only using the features available in the 9i version?
View 4 Replies
View Related
Jun 21, 2011
I have a column.
EMPNO
----------
E123
E256
E420
E1420
I need to get the highest value among these. how to get it?
View 15 Replies
View Related
Jul 18, 2011
How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
View 3 Replies
View Related
Feb 12, 2011
I've a large table on which I applying aggregate and group by functions to the the results.
Here are two of the columns in my table:
Name ==== Score
John ==== 200*
Zohaib ==== 299
Ali ==== 0*
John ==== 200
Maria ==== 150*
Ali ==== 0
Maria ==== absent
John ==== absent
Here astrick (*) means with distinction....
The "score" column is a varchar column I want to run a query on this table to show the highest score for each student and the output should be like this:
Name ==== Score
Zohaib ==== 299
John ==== 200*
Maria ==== 150*
Ali ==== 0*
Important note:
1. if there is a tie between two highest scores like for a student, incase of john 200 was made twice, but the score with asterick (*) will be the "maximum" becuase it is with distinction so the output should also show the the highest score with asterick.
2. the output should show the the 2nd column (score) in desc order of highest score by each student...again incase of a tie, the one with astrick will come first in the result..
I know with just mere numbers, that is pretty easy but in this case it is a varchar column and also i need the astrick along with the highest score.
I want the simplest and shortest query if possible to achieve this result
I hope I've been able to clearly explain my requirment. I am using 10G.
View 1 Replies
View Related
Jun 30, 2010
how to find the nth highest salary
View 11 Replies
View Related
Oct 5, 2005
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example
10,20,25 have a highest common factor of 5.
View 9 Replies
View Related
Apr 29, 2011
i was given a task to find the second highest employee sal from emp table.
View 5 Replies
View Related
Jan 19, 2012
go through both the given queries...
SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);
both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.
View 1 Replies
View Related
Jan 25, 2009
I am trying to update the security_level of a mission to the highest security level of missions of the same type.
Attributes of the missions table:
mission_id, code_name, mission_type_id, mission_date, security_level
The following is an intermediate output.
MISSION_ID MISSION_TYPE_ID SECURITY_LEVEL
318 3 6
329 3 2
286 5 6
521 5 3
281 6 4
396 7 3
331 8 4
14 9 4
230 9 0
486 10 2
The maximum output for each mission_type_id
MAX_LEVEL TYPE
6 3
6 5
4 6
3 7
4 8
4 9
2 10
According to this 3 records (329,521 and 230) should update.
But my code returns an error.
ERROR at line 4:
cannot update (......"SECURITY_LEVEL") to NULL
this is my code
UPDATE
AM_X_442_2 amx
SET
Amx.SECURITY_LEVEL =
(
select
max_level
[code].........
I have intended the query to make it legible but it removes all spaces.
View 8 Replies
View Related
Nov 11, 2010
I want to Bold the value with highest value in Numeric field. how can i do this.
View 12 Replies
View Related
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Aug 28, 2008
How to know the version of PL/SQL Engine
View 1 Replies
View Related
Aug 15, 2013
I have doubts on export
1.) If we have taken exp (traditional export)of schema�s on database version 10.2.0 then shall we imp(traditional export) on database version 11.2.0?
2.) If we have taken expdp of schema�s on database version 10.2.0 then shall we impdp on database version 11.2.0?
3.) If we have taken exp(traditional export) of schema�s on database version 10.2.0 then shall we impdp on database version 11.2.0?
View 6 Replies
View Related
May 31, 2010
How to know the uses to oracl9i version ?
View 2 Replies
View Related
Jun 14, 2012
Can I download & use a version for a Mac using OS X 10.4.11?
View 1 Replies
View Related
Jun 6, 2013
We have several applications hosted on a server using ODP.NET 10, but we are the first to decide to upgrade to ODP.NET 11 which means we need a method where only our application uses ODP.NET 11 without affecting any other existing applications.
I've read about the policy files which automatically redirects application to use latest version installed on the machine. Is there a method available which can avoid this situation as the owners of other application may not allow this to happen.
How can I ensure that my application is using ODP.NET 11 or ODP.NET 10 if both are installed? I created two sample app one with Oracle.DataAccess.dll 10.2.0.100 and other one with Oracle.DataAccess.dll 2.112.3.0. With below line of code, I found both applications are showing Oracle.DataAccess 2.112.3.0 at runtime.
System.Data.Common.DbProviderFactories.GetFactoryClasses();
Last thing, I may be wrong saying latest ODP.NET wouldn't work with NHibernate 1.0.3.0. please validate.
View 4 Replies
View Related
Oct 5, 2012
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
View 7 Replies
View Related
May 28, 2008
difference between oracle version 10.2.0.3 and 10.2.03.I searched a lots of sites to get the details about these versions but unable to collect.
Oracle released both version or 10.2.0.3 and 10.2.03 are same versions treated by oracle.
View 2 Replies
View Related
Mar 17, 2010
Oracle's Software Configuration Manager version 10.1.2.5 with forms version 10.1.2.3.0:
Is this what everybody is using to manage their forms development? If not, what are developers using for Forms 10g version control? I've installed this software. I'm using the Oracle XE database for this repository. At least for running through the tutorial to figure out how this is to work.
I have to say that I am frustrated that I cannot compare one version of a form to another version of the same fmb. I get a java null pointer exception. I have uninstalled the repository using RAU, then recreated it using the RAU. I'm using version control and defining a "large" repository type for more than 10 developers.
View 6 Replies
View Related
Jul 14, 2013
I heard, in RAC environment both clusterware software and oracle software should have similar version.how to find the version of both softwares ?
View 2 Replies
View Related
Apr 8, 2011
How many column can be in a table (9i Version)?
View 1 Replies
View Related
Jan 9, 2013
I have developed my PAYROLL application in forms 9i, which is successfully runnig at internet explorer version 6 but it is not running at internet explorer version 8 or later.
View 1 Replies
View Related
Aug 24, 2012
Oracle 11.2.0.1 and 11.2.0.3.
Can rman refresh database from 11.2.0.1 to 11.2.0.3?
In another words, my source database is 11.2.0.1, my target database is 11.2.0.3
I want to refresh target database from source database. Is it ok since version is different?
View 6 Replies
View Related
Nov 9, 2010
I am trying to install Oracle 9i 32bit client on a windows 2008 server 64 bit OS. In the installation once I select the Oracle home directory and click on Next the whole pc just hangs. Nothing happens. When I checked the Application log I see an error with the following information.
the application (Oracle 9, from vendor Oracle) has the following problem. Oracle 9 is incompatible with this version or windows. For more information, contact Oracle.
View 13 Replies
View Related
Oct 4, 2010
I have one query regarding ORACLE 10g RAC.
I have installed 10gRAC [10.2.0.1] on VMware.
now I have requirement to upgarde it this existing RAC setup to 10.2.0.4.
options for me the steps or sequence I need to follow in upgrade path? like first needs to upgarde crs,then fro ASM and lastly for DB something like this.
View 5 Replies
View Related
