SQL & PL/SQL :: Rank / DECODE Most Popular / Most Used Term
Mar 26, 2012
I have this code which simply gives me the count of term.
SELECT
OUA_ID
,COALESCE (MAX( DECODE( TERM, 0603, TERM_COUNT, NULL ) ), 0 ) "TERM_0603_CNT"
,COALESCE (MAX( DECODE( TERM, 0702, TERM_COUNT, NULL) ), 0 ) "TERM_0702_CNT"
,COALESCE (MAX( DECODE( TERM, 0705, TERM_COUNT, NULL ) ), 0 ) "TERM_0705_CNT"
,COALESCE (MAX( DECODE( TERM, 0707, TERM_COUNT, NULL ) ), 0 ) "TERM_0707_CNT"
FROM(
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
Gives:
OUA_IDTERM_0603_CNTTERM_0702_CNTTERM_0705_CNTTERM_0707_CNT
32710 0 3 7 0
45726 2 0 1 0
Is there a way i could used the rank function (with decode) to only display the 1ST (First) and 2nd(Second) most used term per OUA_ID?
So i get this:
OUA_ID1st MOST USED2nd MOST USED
3271 705 702
45726 603 705
View 4 Replies
ADVERTISEMENT
Mar 24, 2013
I created a sample table named as "Student" with following data. table contains two columns only - stdid & marks.
stdid marks
10 75
20 60
30 60
40 45
50 30
I have to find the rank of students based on their marks in descending order.Is there a way to get rank without using RANK function?
View 11 Replies
View Related
Feb 11, 2011
so here is the query : For every state, list the most popular product. Here is the query I have so far :
SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;
Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2
I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...
View 4 Replies
View Related
Aug 30, 2010
I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).
I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.
Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )
View 8 Replies
View Related
Feb 1, 2011
Currently I have this in the employee table:
Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
220050562500
Is it possible to display the above information like this:
Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
1200802122000
1200803122000
1200804122000
[code]....
View 3 Replies
View Related
Jul 20, 2010
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,
[code]...
View 3 Replies
View Related
Mar 10, 2013
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')
to get:
"OWNER_PARTY_ID"|"MEM_NUMBER"|"SUPPORT_ID"|"MEM_START_DATE"|"MEMBERSHIP_SEQUENCE"
65051|318874751|8014747|01-MAR-10|1
65051|412311060|21502883|15-AUG-12|2
65348|308672459|3526913|01-MAY-10|1
65348|409951130|20950524|18-JUN-12|2
65607|315830192|7510133|17-MAY-10|1
65607|406448110|20024246|16-MAR-12|2
65607|409738130|20903556|14-JUN-12|3
Now I would like to calculate the difference between the start dates of each of the owner_party_id groups, so to get something like this:
OWNER_PARTY_ID|MEM_NUMBER |SUPPORT_ID|MEM_START_DATE |MEMBERSHIP_SEQUENCE|Diff
65051|318874751|8014747|01-Mar-10|1|
65051|412311060|21502883|15-Aug-12|2|898
65348|308672459|3526913|01-May-10|1
65348|409951130|20950524|18-Jun-12|2|779
65607|315830192|7510133|17-May-10|1
65607|406448110|20024246|16-Mar-12|2|669
65607|409738130|20903556|14-Jun-12|3|90
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.
View 4 Replies
View Related
Mar 27, 2013
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
(
[Code]....
View 1 Replies
View Related
Dec 2, 2010
how do i retrieve only last rank row from table ?
View 7 Replies
View Related
Oct 4, 2013
I've below table.
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]....
View 3 Replies
View Related
Jul 13, 2011
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.
View 7 Replies
View Related
Dec 2, 2010
the following statement gives each customer owns how many promotions.
Q:) how do i retrieve a customer who has max. promotions?
SELECT C.CUSTOMER_ID,COUNT(P.PROMOTION_ID)
FROM PROMOTIONS P,CUSTOMERS C
WHERE C.CUSTOMER_ID = P.CUSTOMER_ID
GROUP BY C.CUSTOMER_ID
=====================================================
CUSTOMER_ID COUNT(P.PROMOTION_ID)
-------------------------------------
001 | 5
002 | 8
003 | 4
004 | 6
005 | 5
006 | 3
View 7 Replies
View Related
Feb 1, 2012
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;
col1rank
------------
a1
b2
a3
c4
View 8 Replies
View Related
Aug 30, 2012
I have the following table :
CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
[code].......
I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);
I would like to use the rank function.
Desired update:
A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4
View 5 Replies
View Related
Mar 17, 2013
can we create rank on a particular column without using rownum and rank function.
View 9 Replies
View Related
Jan 5, 2012
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.
View 11 Replies
View Related
May 1, 2013
Here table - tac has
row1
-----
X
X
X
B
[code]...
I want ouptut like using sql query with out using dense_rank function,
row1 row2
X 1
X 1
X 1
B 2
[code]...
View 7 Replies
View Related
Oct 22, 2012
I am attempting to user PIVOT and DENSE RANK in a query the following is the query and the record set it returns (condensed and de-identified)
"select * from(
select * from (select dense_rank() over (partition by 1 order by cal.weeksort desc) WEEK_nbr,
u.user_title Manager_Title, replace(hier.manager, '<br>',' - ') Manager,
replace(hier.user_hin, '<br>',' - ') user_name,
to_char(cal.calendar_date_week - 6, 'MM/DD/YYYY') ||' - '|| to_char(cal.calendar_date_week, 'MM/DD/YYYY') Week_of,
upper(substr(cal.day_of_week, 1,3)) DOW, count(distinct Pers_gen_key) cnt from apexim.hrw_member_action act
[code]....
View 2 Replies
View Related
Sep 1, 2010
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'
View 3 Replies
View Related
Nov 16, 2009
Which warehouses have pending orders for products, which are not in stock at the warehouse at the moment? Provide warehouse number, id of the product that is not in stock, number of orders issued for this product and total quantity ordered.
The tables I am using are
Warehouses:
Name Type
----------------------------------------
W_ID NUMBER(38)
CITY VARCHAR2(20)
W_SIZE NUMBER(38)
Inventories:
Name Type
-----------------------------------------
P_ID NUMBER(38)
W_ID NUMBER(38)
QUANTITY NUMBER(38)
Orders:
Name Type
-----------------------------------------
ORD_ID NUMBER(38)
SUPPLIER_ID NUMBER(38)
ISSUING_EMP_ID NUMBER(38)
ORDER_DATE DATE
ORDER_STATUS CHAR(1)
This is my code so far:
select w.w_id, i.p_id,
sum(decode(o.ord_id, ' ', i.p_id, 0)) Orders_issued,
select sum(i.quantity)
from
inventories i
orders o,
[code]...
but I get this error:
select sum(i.quantity)
*
ERROR at line 3:
ORA-00936: missing expression
View 2 Replies
View Related
Sep 7, 2011
Just working with Unix for the first time and trying to understand this decode statement?
cursor l_cursor is select decode(type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1','/'||chr(10), null) ||
decode(line,1,'create or replace ', '') ||
decode(type||'-'||to_char(line,'fm99999'),
'JAVA SOURCE-1','and compile ' || type || ' named "' ||
name || '"' || chr(10) || 'AS' || chr(10), '') ||
text text
from user_source
where upper(name) = upper(p_name)
order by type, line;
View 8 Replies
View Related
Feb 28, 2011
In my Hard code i saw decode like this
DECODE(tablename.columnname,'k',text,NULL,text, NULL)
can I use decode like this.
View 6 Replies
View Related
Jul 1, 2007
im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.
View 6 Replies
View Related
Jul 12, 2013
I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
View 5 Replies
View Related
Aug 3, 2013
I have a scenario where in report table I need to get
IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.
I tried using decode in WHERE CLAUSE , but not successful.
Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.
Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.
Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.
Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...
View 14 Replies
View Related
Feb 13, 2011
How to use decode and case in "where" and "from" clause of a select statement.
I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition
View 7 Replies
View Related
Jul 28, 2010
I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.
What do I have to do to get the data on one line? My code is as follows:
SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...
View 5 Replies
View Related
Aug 11, 2011
I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"
select distinct record_number
from meta m, procedu b, control v, fact f, calendar dc, person p
where f.key = m.key and f.group_key = b.group_key
and b.proc_key = v.proc_key
and f.calendar_key = dc.calendar_key
and f.person_key = p.person_key
and VALUE BETWEEN DECODE((Select distinct operator_type
[code]....
But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"
View 4 Replies
View Related
Oct 31, 2011
How can i use the decode function?
for example
I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?
View 9 Replies
View Related
Apr 4, 2013
DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')
what does it mean..
View 3 Replies
View Related