SQL & PL/SQL :: Distinct With Orderby In Subquery

Mar 31, 2010

The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:

SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid

But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.

SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)

Is there any way to select the DISTINCT taskids ordered as per requirements?

View 13 Replies


ADVERTISEMENT

Using Both OrderBy And RowNum In Query

Nov 22, 2012

I tried to make my Query as simple as possible but also it contains the problem:

SELECT A.Id,A.Attachment,A.CreateDateTime,A.No
,rownum as RowNumber
FROM (
SELECT A.Id,A.Attachment,A.CreateDateTime,A.No FROM GOutgoingLetter A ,
(
SELECT B.Id, B.createdatetime as of0 FROM GOutgoingLetter B
WHERE
Exists
[code]...

when I use Both Order By And RowNum in my Query, Two columns of final Select Are Null: NO & Attachment. whereas this Columns aren't empty.when I comment each one of "ORDER BY B.of0" or ",rownum as RowNumber " everything is correct!!

View 1 Replies View Related

Retrieve Distinct Values From Clob Using Distinct Operator

May 27, 2013

i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work

View 1 Replies View Related

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 Replies View Related

SQL & PL/SQL :: From Analytical To Subquery

Jun 10, 2010

I was reading a tutorial for analytical function and i found something like this

sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)

How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.

View 12 Replies View Related

SQL Subquery And Aggregation

Nov 6, 2010

I have a database containing the following after entering the following sql command

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey

TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman

17 rows selected.

What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties

SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5

TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198

17 rows selected.

So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32

View 6 Replies View Related

Distinct On Presorted SQL

Oct 7, 2010

I have a function which returns a preformatted SQL but with duplicates as follows

FSI
..FSIL
..FSIL
....IS123
....IS123
....IS345
....IS345
....IS547
....IS547
..FSIR
..FSIR
....IS98777
....IS98777
....IS34567
....IS34567
....IS67799
....IS67799

I have to eliminate the above result set without changing the order. a distinct on the function returns a jumbled result set.

View 1 Replies View Related

SQL & PL/SQL :: Distinct Value From VARRAY?

Feb 21, 2012

I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.

declare
type t is varray(10) of varchar2(10);
t1 t;
type r is table of varchar2(10) index by binary_integer;
r1 r;
begin
t1 := t('A','B','A','B','A','B','C');
select distinct * into r1 from table(select * from t1);
END;

View 1 Replies View Related

Single Row Subquery Returns More Than One Row

Dec 6, 2012

I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.

select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id

View 2 Replies View Related

Single-row Subquery Returns More Than One Row?

Dec 7, 2009

Just trying to update a table in which the sales amount is inserted when the sales amount is null.

I have

UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )

View 2 Replies View Related

Limiting Subquery To First Record?

Oct 1, 2007

restricting a subquery's results to the first record. If I use the following:

SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE

I get

-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07

But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.

View 4 Replies View Related

Single-row Subquery Returns More Than One Row

Jan 10, 2012

I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.

 UPDATE ps_ntsr_gf_stufile a
    SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
    FROM PS_CLASS_TBL b
      WHERE a.CLASS_NBR = b.CLASS_NBR
      AND a.STRM = '1118'
      AND a.INSTITUTION = 'NT752')
      WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL); 

I'm still getting the ORA-01427 error.

View 1 Replies View Related

SQL & PL/SQL :: Error ORA-00904 While Using Subquery?

Jan 8, 2013

I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.

CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;

[Code].....

In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?

View 8 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row

Mar 20, 2012

/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/

when i am trying to run this query i am getting an error'single row subquery returns more than one row'.

View 2 Replies View Related

SQL & PL/SQL :: Single-row Subquery Returns More Than One Row

Jul 30, 2010

My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :

SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....

SQL return : Ora-01427 Single-row subquery returns more than one row

Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,

View 13 Replies View Related

SQL & PL/SQL :: Single Row Subquery Returns More Than One Row?

Apr 28, 2012

I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept

It results in the below error.

[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row

I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)

I have written the below sql to fetch all loc for emp which got executed

SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP

But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.

View 3 Replies View Related

SQL & PL/SQL :: Using Subquery In Insert Statement

Jun 5, 2010

I read in a book that you can't use subquery in an insert statement . E.g:

1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:

2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');

This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.

View 5 Replies View Related

SQL & PL/SQL :: Subquery In Column List

Jun 5, 2011

A SINGLE SQL QUERY...

i have tried with no luck!!

consider this sample table:

NAME GENDER MARRIED
---------------------------
AAA M Y
BBB M N
CCC M Y
DDD F Y
EEE F Y
FFF M N

OUTPUT SHOULD BE:

GENDER TOTAL MARRIED
------------------------
MALE 4 2
FEMALE 2 2
TOTAL 6 4

View 24 Replies View Related

PL/SQL :: Comparing Two Counts Using Subquery?

Feb 10, 2013

Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.

Eg:

R(x,y)
Select count(x)
from R
group by x;

Then I want to run another query to determine which x's have the same count value and output these corresponding x's.

View 6 Replies View Related

PL/SQL :: Use A Subquery After Update Keyword?

Oct 18, 2012

Can we use a subquery after the update keyword??

For example

UPDATE ( Select col1 from test)
set col1='x';

In short can we replace the table name after an update and use a select statement instead??

View 3 Replies View Related

PL/SQL :: Query Using Subquery And Joins

Feb 7, 2013

I want to display first joined and last joined employee name(first_name) in each department like this

department_name | first joined employee | last joined employee

I think we have to use joins and sub query.

employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Select Just 1 Row For Each Distinct Column Value?

Feb 16, 2011

i need a Select * from tablename just 1 Row for each distinct ActionMode column value

CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Getting DISTINCT Count From Two Different Columns?

Feb 1, 2012

I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.

SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;

This query gives following result

3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR

There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result

3525125350000 2

View 9 Replies View Related

SQL & PL/SQL :: Use Distinct For Multiple Fields?

Oct 18, 2011

i have a query in this way

select field1,field2,field3 from Table1
union
select field1,field2,field3 from table2

In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...

View 3 Replies View Related

SQL & PL/SQL :: Distinct Values In A Column?

Jul 18, 2013

I have a table with two columns, like:

123 xxx
456 xxx
789 yyy
987 yyy

And in the output I would like to have:

123 xxx
789 yyy

I tried with distinct and unique on the second column, but it doesn't work.

View 12 Replies View Related

SQL & PL/SQL :: Multi Column Distinct

Sep 16, 2010

I need to select multiple columns but only have 2 of them which are distinct. For instance if i have

userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55

what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Distinct Cat_id

Nov 1, 2011

Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)

Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER

select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc

How can I retrieve distinct cat_id here?

View 21 Replies View Related

SQL & PL/SQL :: Distinct With Order By Clause?

Mar 13, 2012

I have got the following error while executing below Query.

ORA-01791 'Not a SELECTed expression'

select distinct sgbstdn_levl_code
from sgbstdn,spriden
where spriden_pidm = sgbstdn_pidm
and spriden_id = '200076543'
order by sgbstdn_term_code_eff desc;

The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.

View 14 Replies View Related

SQL & PL/SQL :: Distinct Timestamp - One Entry Per Day?

Apr 21, 2010

how can I distinct this query to get just one entry for one day.

this query:

select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp

returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.

but i want to have:

Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Table With Distinct Value

Sep 4, 2012

select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'

when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?

View 1 Replies View Related







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