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

PL/SQL :: Any Way To Get Rank Of Students Without Using RANK Function?

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

SQL & PL/SQL :: List Most Popular Product For Every State?

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

SQL & PL/SQL :: String Together All Term IDs For ATM

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

SQL & PL/SQL :: Term Breakdown

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

SUM TOP N Values Using Rank?

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

PL/SQL :: Using Lag And Rank In The Same Query

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

SQL & PL/SQL :: Returning First Rank After Aggregating

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

SQL & PL/SQL :: Retrieve Only Last Rank Row From Table?

Dec 2, 2010

how do i retrieve only last rank row from table ?

View 7 Replies View Related

SQL & PL/SQL :: Rank Based On Multiple Conditions

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

SQL & PL/SQL :: Way When Doing Rank Function In PL/SQL To Pass Field

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

SQL & PL/SQL :: Join Query - Retrieve Last Or First Rank Row?

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

SQL & PL/SQL :: Rank Result With Respect To Order

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

PL/SQL :: Rank Function And Duplicate Records

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

SQL & PL/SQL :: Create Rank On Column Without Using Rownum Function

Mar 17, 2013

can we create rank on a particular column without using rownum and rank function.

View 9 Replies View Related

SQL & PL/SQL :: Using Rank Clause With Window Aggregate Functions

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

PL/SQL :: Output Like Using Sql Query Without Using Dense-rank Function?

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

PL/SQL :: Attempting To User PIVOT And DENSE RANK In A Query

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

Reports & Discoverer :: Pick Latest Record Rank And Count?

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

How To Use Decode And Sum

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

SQL & PL/SQL :: What Does DECODE Do

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

SQL & PL/SQL :: Use Of Decode

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

Alternative To DECODE In Regards To Just SQL (not PL)

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

SQL & PL/SQL :: Decode With Like Operator

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

SQL & PL/SQL :: Decode In Where Clause

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

SQL & PL/SQL :: Case Vs Decode?

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

SQL & PL/SQL :: Decode On One Line

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

SQL & PL/SQL :: Decode With Between Clause

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

SQL & PL/SQL :: How To Use Decode Function

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

SQL & PL/SQL :: Decode Function

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







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