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?
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.
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
can we create rank on a particular column without using rownum and rank function.
View 9 Replies View RelatedHere 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]...
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]...
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.
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]....
how do i retrieve only last rank row from table ?
View 7 Replies View RelatedI'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]....
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
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
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
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.
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]....
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 need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065
here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number
The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.
i have a table students create table students (name varchar2(10),rolno number(10),sub1mark number(10),sub2mark number(10),total number(10),percentage number(10),status varchar2(10))
i am go to create trigger on students after entering the values of name,rolno,sub1mark,sub2mark the values of total & percentage come automatically .trigger is
CREATE OR REPLACE TRIGGER UCSETH.students_comm_trig
BEFORE INSERT ON UCSETH.STUDENTs FOR EACH ROW
BEGIN
set NEW.total = new.sub1mark+new.sub2mark;
set new.percentage=new.total/2;
[code]....
I have been asked to show the names and room number according to students who have 4 classes in a room.
select s.name, r.room_no
from s, r
where r.room_no = u.room_no
and e.unit_code = u.unit_code
GROUP BY s.name, r.room_no
HAVING COUNT(DISTINCT s.stu_no) > 4;
However I get the error 'ERROR at line 4:
ORA-00904: "U"."UNIT_CODE": invalid identifier'
I have a SQL link table called student_monitor containing the follwing attributes and e.g. data
student_id, Class_id, Predicted_Grade, Actual_Grade
1 1 A C
2 1 B B
3 1 C B
I need to be able to flag which students are under achieving on their target grades and dont know how to do this or the best way to do this as i cannot calculate the diff between a char?
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
I have the following C code:
class Factorial {
public:
int getVal (int a);
};
[code]....
/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.
Just in case, listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....
What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.
View 2 Replies View RelatedWhat is advantage of Deterministic function over normal function?
What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies View RelatedI have the following query.
sql
Original
- sql Code
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class
SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN ( SELECT l_id FROM n_table WHERE id IN (1234)
GROUP BY class
ORDER BY class
It returns two columns: class and the total number of values in that class.
In some cases, there might not be any values under a certain class, so the query won't return anything.
In such a case I want the query to return 0. So what I want to see is:
class:A, COUNT(class):0
I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.
sql
Original
- sql Code
SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
[Code]....
I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).
With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.
Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.
My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below
myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)
[code]...
can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?
View 2 Replies View Related