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


ADVERTISEMENT

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

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

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

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

Retrieve Auto Increment Field Value With Left Join?

May 27, 2013

I have already done auto increment by making sequence and trigger. but now the problem is when i am trying to retrieve data from that table it returns all data perfectly but the primary key that is my auto increment value shows blank.I am facing this problem with join query, there 4 table left joined in my query. But when I remove join from my query then it shows that value.

But i need that value in my join query.So, what is the problem and what can I do?And other thing is when I apply this query in Oracle SQL Developer, it works perfect.

My Query:
return $this->db->query("select * from TBL_EMPLOYEE_BASIC left join TBL_EMPLOYEE_DETAILS on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_DETAILS.EMPL_ID left join TBL_EMPLOYEE_EDUCATION on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EDUCATION.EMPL_ID left join TBL_EMPLOYEE_EXPERIENCE on TBL_EMPLOYEE_BASIC.EMPL_ID = TBL_EMPLOYEE_EXPERIENCE.EMPL_ID where

[code]...

View 2 Replies View Related

PL/SQL :: Function Retrieve Output Very Slowly - Alternative Code To Replace With Join?

Aug 6, 2012

In my project, the below function retrieve output very slowly. Is there any alternative code to replace this function with join

CREATE OR REPLACE FUNCTION f_johnson (i_case_id number,i_seq_num argus_app.case_reference.seq_num%type) RETURN VARCHAR2 AS
c_ref VARCHAR2(32500) := null ;
CURSOR c_refer IS

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Query To Retrieve Value In Specified Schema

Jan 6, 2011

I need a query to retrieve a particular string from all the columns and all tables in a specified schema.

For example:i had a value "Current" in a particular column in a particular table. i need to find this value if i don't know the table name and column name.

View 2 Replies View Related

SQL & PL/SQL :: Query To Retrieve The Data Without Iteration?

Dec 27, 2010

I have one table as shown below,

CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);

INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);

The data will look like this .

matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800

I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.

Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200

Can we make use of oracle lag and lead functions here?Or some other methods?

View 6 Replies View Related

SQL & PL/SQL :: Query To Retrieve Records In Frequency Of Days?

Dec 18, 2012

I have a following requirement in SQL -

Requirement -

I need to list the purchase Ordrers which are not closed.

my sql query should pull if the PO is not closed in 30,90 and 150 days.

It should be shown only on 3oth,90th and 150th day only even the Purchase order is not closed on 33rd day.

View 1 Replies View Related

Reports & Discoverer :: How To Execute Query 2 If 1 Doesn't Retrieve Anything

Dec 3, 2010

In oracle report how do we execute query2 if query1 doesn't retrieved any records.

View 4 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Build Query To Retrieve Records Between Dates With Same Field?

Mar 17, 2010

i have a doubt in building a query.

I have a table with fields

job_no activity Date
101 anchorage 20/01/2010
102 berthing 25/01/2010
103 sailing 29/01/2010

If i want to know the status of the ship on the date '22/01/2010' It has to show as 'anchorage', becoz on '25/01/2010' only it came to berthing from anchorage. How to write a query to achieve this.

View 12 Replies View Related

TimesTen In-Memory :: Query To Retrieve Previous Day Data

Jun 13, 2012

I would like to have a query which should fetch previous day records from column which is having timestamp data type.

select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;

But this gives output not for previous day, but all records which are 24 hrs less than current day. How to get records for previous day based on column having timestamp data type.

View 4 Replies View Related

SQL & PL/SQL :: Query To Retrieve List Of Order_id Which Has No Records In Database

Jul 20, 2010

Its a simple query to retrieve data with the Order_ID.If the record is there its fine and can retrieve the data. But my query is to retrieve the list of order_id which has no records in database.

View 12 Replies View Related

SQL & PL/SQL :: Query On Self Join

Nov 13, 2011

The existing format of the records are

MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107

This should be modified as below.

MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107

[Code]..

Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.

View 5 Replies View Related

SQL & PL/SQL :: Query Without Outer Join

Nov 2, 2012

I have an attendance table in which we have empno, time_in, time_out..etc.

I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.

I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?

View 7 Replies View Related

SQL & PL/SQL :: Table Join Query

Nov 10, 2010

I have the following 2 tables.

A) Docversion
CREATE TABLE DOCVERSION
("OBJECT_ID" VARCHAR2(250 BYTE),
"OBJECT_CLASS_ID" VARCHAR2(250 BYTE),
);

[Code]..

Join column : object_id and parent_id.

The object id in docversion will have multiple values for element value in listofstring table.

ordinal value represents teh sequence of element value

Eg:

data for docversion:

1 23
2 34

data for LISTOFSTRING:

1 11 0 100
1 11 1 109
1 11 2 119
2 22 0 A
2 22 1 B

ouptut:

I want the output as follows

docversion.objectid,listofstring.elementvalue
1 100,109,119
2 A,B

View 14 Replies View Related

SQL & PL/SQL :: Update Query Using Join

Jun 26, 2012

How to update single table column using join query

Example:

Update table1 t1,table2 t2
set t1.column2 = 'Y'
where t1.column1 = t2.column1

View 8 Replies View Related

SQL & PL/SQL :: Join In A Query Which Has (+) Symbol At End

Oct 26, 2011

I have come across a join in a query which has the (+) symbol at the end of it.

View 3 Replies View Related

PL/SQL :: One To Many Relationship In A Query Via Join

Oct 11, 2013

I have been told to create a query which should give out the same output as the UI of an exception management application .  However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .

View 4 Replies View Related

Alias In Inner Join Query

Feb 27, 2008

In my Oracle 9i Schema, I have two tables:

Table One

pocOne pocTwo
2 3
2 4
1 2

Table Two
TableTwoId Name
1 Jones
2 Smith
3 Edwards
4 Camden

My SQL to fetch all records with Smith works great:

select Name from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo)
where Name = 'Smith'

Now I need to create an alias for the Name field. Here is my attempt:

select myAliasName from TableTwo
Inner Join TableOne
on TableTwo.TableTwoId in (TableOne.pocOne, TableOne.pocTwo),
(select Name as myAliasName from TableTwo)
where myAliasName = 'Smith'

This attempt pulls up all the records instead of just Smith records. How I can create an alias for the Name field in my above query?

View 3 Replies View Related

SQL & PL/SQL :: Convert Join Sub Query

Sep 10, 2012

I want to convert join to subquery. how cna i do this.

FROM
TABLE_1 A
INNER JOIN TABLE_2 B
ON ( A.COL_1 = B.COL_1)

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: Query To Join Two Date Fields?

May 19, 2011

TABLE - 1

CIDDOB(DATE)
12312-MAR-63
58918-JAN-78
658927-DEC-43
46515-FEB-80

TABLE - 2

DIDDOB_INFO(VARCHAR2)
34425 Mar 1967
123 12 MAR 1963;25 FEB 1974;25 AUG 1978
46515 FEB 1980

I want to join the columns DOB from table -1 and DOB_INFO from table 2 but the datatype of DOB is DATE and DOB_INFO is VARCHAR. TO_DATE function is not working here.

View 13 Replies View Related

Performance Tuning :: When To Use Sub-query And When To Use Join

Dec 14, 2010

In SQL, almost all the thing which are possible with join is possible with sub-query also and vice-a-versa.

So when should I use sub-query and when should I go for join?

View 9 Replies View Related

SQL & PL/SQL :: How To Use Outer Join Condition In A Query

Jun 27, 2013

How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.

I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.

SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......

View 10 Replies View Related

SQL & PL/SQL :: Joining A Query Instead Of Using Left Join?

Jan 20, 2011

joining this query instead of using the left join. Reason is want to show the score column in a different place and also do not want to show the second IPS column that is used in the joined query.

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

select * from
(
select i.ips,
p.project_name,
p.project_segment,p.location,p.project_exec_model,
p.project_exec_model||' - '||p.project_config pmodel,
one.score schedule,two.score cost,three.score execution,four.score commercial,
nvl(one.score,0)+nvl(two.score,0)+nvl(three.score,0)+nvl(four.score,0) as total,

[code]....

View 6 Replies View Related

No Join In Query Involving 2 Tables

May 13, 2013

I saw a strange plan for one query in TESTING DB today. Although 2 tables are involved i dont see any join , NL/HJ/SMJ !! i am not facing any performance issue but curious to know what type of optimization oracle is doing here.

Query text and plan :
SELECT FIRST_NAME
  FROM CAMPA.TABLE_A
WHERE NAME_ID =
          (SELECT NAME_ID
             FROM CAMPA.TABLE_B
            WHERE ban=:b1);
[code]....

View 7 Replies View Related

PL/SQL :: Avoid Cartesian Join In Query?

Oct 24, 2012

must generate a Cartesian join, but I do not know why it happens. dt.debtorkey, cl.clientkey, inv.invoicekey, ag.agingkey are primary keys of each table. The problem is: I got same tuple for 8 times.

select dt.debtorkey, cl.clientkey,  inv.invoicekey, ag.agingkey, dt.DebtorNo, dt.Name as "debtor Name", dt.State,  cl.ClientNo, cl.Name as "Client Name",  inv.InvNo, inv.PurchOrd, inv.Amt,
to_char(inv.InvDate, 'MM-DD-YY') invoice_date,  to_char(ag.DateLastBuy, 'MM-DD-YY') aging_lastbuy, to_char(ag.DateLastPmt, 'MM-DD-YY') aging_lastpmt

[code]...

View 14 Replies View Related







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