I have a SQL statement that return the following result.
col1
----
a
a
b
b
a
c
c
c
c
What i need is to eliminate the duplication and return the result as the following.
col1
----
a
b
a
c
Using group by or distinct will eliminate the third row "a" and will not return it ordered.My idea is to generate another column that rank the result as following.
col1rank
------------
a1
a1
b2
b2
a3
c4
c4
c4
c4
so using the following SQL query will return the result as needed
select col1, rank
from T
group by col1, rank
order by rank;
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
I have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.
I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.
Does ascending index ensures that query without order by will have the result set sorted?
E.g. the query is
select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1); column odm_type has index created like this (default is ASC): create index ODM_UID_I on table_t (ODM_UID);
Will such a query always return the first record having the minimal odm_uid in all Oracle versions?
I have two tables tab1 and tab2 with below data Now requirement is to find difference between two table with respect to column "serial_no" which exist only in table tab1 so as per below at serial_no 2 CC4 in tab1 but CC2 in tab2 so this is different how to dump out this data
insert into tab1 values('P1','CC1',101,1); insert into tab1 values('P1','CC4',104,2); insert into tab1 values('P1','CC5',105,3); insert into tab1 values('P1','CC2',102,4); insert into tab1 values('P1','CC3',103,5);
insert into tab2 values('P1','CC1',0,101,102); insert into tab2 values('P1','CC2',101,102,103); insert into tab2 values('P1','CC3',102,103,104); insert into tab2 values('P1','CC4',103,104,105); insert into tab2 values('P1','CC5',104,105,0);
I have a multiple data block. There is a list item present on the form having three types INVOICE,BOE and MANUAL. When I select 'MANUAL' , that LOV should not appear for a particular item on the form and the user should be able to insert the value manually and validate manually. I did use set_lov_property, but did not get the correct usage.
I think set_item_property with VALIDATE_FROM_LIST property won't work as it sets the same property to whole column once it is fired. So if I write set_item_property for MANUAL it would set the the column, VALIDATE_FROM_LIST property at once when it is fired, not allowing me to the set properties for that column according to INVOICE and BOE where I would require the LOV. I want it to go with the item instance and therefore opting for set_lov_property but not clear with the syntax given
1) SET_LOV_PROPERTY (lov_name VARCHAR2, property NUMBER, value NUMBER);
2) SET_LOV_PROPERTY (lov_name VARCHAR2, property NUMBER, x NUMBER, y NUMBER)
I have 20 item fields in same form. i need to align them in sequential manner. For example x y z abcl m o p q r s xyzabclmopqrs are the items to achieve this what i have to do...
I certainly know this is possibly but I am trying to do this on the fly and can't seem to work it out:
I have a table A: ID Name Priority ------------------- 1 Smith 1 1 SSmith 2 1 ASmith 3 1 BSmith 3 2 John 2 3 Ed 1
and I am looking to create the following table from this: ID Name Sum(Top3Priority) -------------------------------- 1 Smith,SSmith,etc 8
Now, I've got listagg working and everything appears to be going swimmingly but: for every listagg grouping on name I need to only sum the highest top 3 priorities. So in the example above there are four Smiths but I need to only sum the top 3 priorities which are 3,3,2 and ignore the 1 even though I do want all the listagg Smith's (SSmiht, ASmith, etc) in there.
Now I can sum the priority, but don't really know how to sum only the top 3 in any ID ? There can be 1 to n ID's so if there are only 2 ID's I want to sum those 2, if there are 3 all 3 and 4 upwards only the top 3.Here is a snippet of the SQL I am using
SLECT id, listagg(MN_CR_LOOKUP.f_name, ',') within group (order by Priority)) roadname, **** sum top 3 here ?**** count(*) "NumI", Sum("Elevation") "CombinedElevation" FROM jc,
I am trying to find out the difference in time between peoples memberships and also the order that these memberships are taken out in. So far I have added in a rank statement to work out the order the memberships were created in, but now want to look at the difference between the dates returned. The SQL I used is:
SELECT owner_party_id, mem_number, support_id, mem_start_date, RANK() OVER (PARTITION BY owner_party_id ORDER BY mem_start_date ASC) MEMBERSHIP_SEQUENCE FROM membership_all WHERE version_type = 'CUR' AND owner_party_id IN ('65051', '65051', '65348', '65348', '65607', '65607', '65607')
I think that I need to use the Lag function in, but I am not too sure if it can be linked to look at the data within a grouping of owner party id, as it would make no sense to calculate the difference in dates for two different owner party ids.
I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...
select hour, calls from ( select hour, calls, rank() over (ORDER BY calls desc) as ranking from (
with tmp_tbl as (select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, [code].....
Now i need to rank each cust_id in each hh_id based on below conditions.
1) If atleaset one cust_id in hh_id has f_rnk then gender 'F' with highest f_rnk (more then one F with same f_rnk then the one with oldest first_dt), if no 'F' then gender 'U' with highest f_rnk ((more then one F with same f_rnk then the one with oldest first_dt)), if no 'F' and 'U' then consider 'M' (more then one M with same f_rnk then the one with oldest first_dt).
2) If the above is not met (no cust_id in hh_id has f_rnk populated) then i've to rank based on purch_dt. Gender 'F' with recent purch_dt (if more than one F in household with same purch_dt then the one with oldest first_dt), if no 'F' then gender 'U' with recent purch_dt (if more than one U in household with same purch_dt then one with oldest first_dt), if no 'F' and 'U' then consider 'M' (more than one M in household with same purch_dt then the one with oldest first_dt).
3) If the above criteria is also not met, then rank based on gender_cd. Gender 'F' will have first preference then 'U' and then 'M'.
My output :
HH_ID CUST_ID F_RNK GENDER PURCH_DT FIRST_DT F_RNK_RANK PURCH_RANK GENDER_ONLY_RANK ----- ------- ---------- ------ ----------- ----------- ------------ ------------ ----------------- H1 C1 2 F 1/2/2013 2/3/2010 1 H1 C2 1 M 1/2/2013 3/3/2010 2 H1 C3 U 1/3/2013 4/3/2010 3 H2 C4 F 4/3/2013 10/2/2009 2 H2 C5 M 5/5/2013 8/8/2010 1 H3 C6 F 5/6/2008 1 H3 C6 M 7/8/2010 2
I've tried below query with one condition, but it's giving f_rnk_rank for all records. How can i include multiple conditions in the rank function.
with tmp_tbl as (select 'H1' as hh_id, 'C1' as cust_id, 2 as f_rnk, 'F' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100203','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C2' as cust_id, 1 as f_rnk, 'M' as gender, to_date('20130102','YYYYMMDD') as purch_dt, to_date('20100303','YYYYMMDD') first_dt from dual union select 'H1' as hh_id, 'C3' as cust_id, cast(null as number) as f_rnk, 'U' as gender, to_date('20130103','YYYYMMDD') as purch_dt, s hh_id, 'C5' as cust_id, [code]....
there is a way when doing the Rank Function in PL/SQL to pass the field that will be ranked as an override.
SELECT rank(p_ColumnAmt) within group (order by p_ColumnNm desc) rank INTO v_RnkNoAmt FROM Table_name WHERE ??????;
p_Column is the amount I am ranking p_ColumnNm is the actual field name to Rank.
When I pass the field name with an override I do not get the correct rank back. If I run the Select with the actual field name curr_1_mth_amt, I get the correct rank.
I have about 70 different field to Rank and do not want to make a procedure for each field.
SELECT DISTINCT OUA_ID, TERM_COUNT, TERM FROM( SELECT OUA_ID, TERM ,PROVIDER_CDE, COUNT(*) TERM_COUNT FROM TABLE WHERE PROVIDER_CDE = 'BILL' GROUP BY OUA_ID, TERM, PROVIDER_CDE)) GROUP BY OUA_ID
I am trying to use RANK() clause with a window clause...is that possible to use both together?
like
select col1, col2, col3, RANK() OVER (ORDER BY col3 desc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RK from table t
but getting error in SQL Developer
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause: *Action:
Error at Line: 2 Column: 33
The reason why i need to rank in window clause because i have data like this
Name Marks Quiz Ali 10 1 John 20 1 Sara 30 1 John 40 2 Sara 50 2 Ali 20 2 ... ... and so on
I want to rank them based on their cumulative sum marks after every test..ranking should be in such a way that it should look current row and preceding rows
like this
Name Marks Quiz cumulative_marks rk Ali 10 1 10 4 John 20 1 20 3 Sara 30 1 30 2 Peter 100 1 100 1 John 40 2 60 3 ==> becuase John has now third most overall cumulative marks (60) after quiz 2. Sara 50 2 90 2 ==> becuase Sara has now 2nd most overall cumulative marks (90) after quiz 2. Ali 20 2 30 4 ==> becuase Ali has now fouth most overall cumulative marks (30) after quiz 2.
Am working on a workbook to count the number of enrolments and withdrawals in the program. My data looks like this
name semester status year A 1 enrol 2010 A 2 withdraw 2010 A 3 enrol 2010 B 1 enrol 2010 B 2 withdraw 2010
I want to count their latest status only. It should come up with Total Enrol - 2 Total Withdrawn - 1
For total Withdrawn, I tried 'rank' and filter to equals 1 but it does not allow me. Is there any way to have this work? Here's my calculation:(decode((FIRST_VALUE(status) OVER(PARTITION BY year, name ORDER BY semester DESC)),'withdraw', name)) It tells me that 'Aggregation of Analytic function not allowed'
I am having table without any primary key. In this table, only inserts and deletes are performed , no update operation.
Is it safe to use order by rowid on such a table ? Does by applying order rowid, is it possible to check order in which rows were inserted in this table ?
Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?
What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?
select empno,ename,rownum from emp order by rownum desc;
I rearranged the fields on a form, so I want the tab order to be different. I rearranged the fields in the object navigator to be in the order I want them to tab. I made sure the Previous Navigation Item and Next Navigation Item were set to null for all the fields.
Still it keeps the old tab order.
I also tried explicitly specifying the Previous Navigation Item and Next Navigation Item. Same thing, it kept the old tab order before I rearranged the fields. Previous Navigation Item and Next Navigation Item are both null.
There are no triggers on these fields. All fields are in the same block. I am using Oracle Form Builder version 9.
I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me
I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.
