PL/SQL :: First Clause With Order By

Apr 29, 2013

I have a requirement where I need to display a list of employees, performance rating and manager id. I want to display the employees first whose manager is as manager_id as input parameter. So lets say a manager logs in, he should see his reporters first and then the rest. How can I implement this in SQL? I am trying first clause with order by but not sure how to do that here.

procedure
display_list(manager_id in varchar2)

Output is:
emp_id
manager_id
performance_rating

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Order By Clause?

Mar 12, 2013

I have requirement to create an XML structure through stored procedure. I need to Order some of the columns in ascending order before I format them into the xml structure. I am pretty novice to creating an output into XML format, but attached is the query I came up with (without order by). This works perfect, but now the requirement is to order by - cls_cd, and within cls_cd, again order by - cat_cd. I am not able to do this.

View 2 Replies View Related

SQL & PL/SQL :: Results Different After Using Order By Clause

Mar 18, 2011

Result are different after adding the Order By clause in below query.

Query

select sum(wpsv1.primary_qty) detail_qty,
wpsv1.from_subinventory subinventory,
wdd.revision revision,

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: Significance Of Order By Clause?

Nov 4, 2012

I know that order by clause in normal query is used to order the result set based on the key. significance of order by clause in analytical functions.

with data as
(select empno,
sal,
case

[Code]...

In case of min, max analytical functions order by clause have importance. But how it will work for a COUNT function ?

View 1 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

PL/SQL :: Unpivot Using Order By Clause

May 15, 2013

explain the difference in the VALUE column when DESCRIPTION = 'dept' when the query is run with and without the order by clause.

SELECT * FROM
(
SELECT * FROM emp
--ORDER BY job
)UNPIVOT (VALUE FOR DESCRIPTION IN (sal AS 'salary', deptno AS 'dept'));

View 12 Replies View Related

SQL & PL/SQL :: ORDER BY Clause And ROWNUM

Apr 4, 2010

When i try to execute a query, which is organised as the below example, it retrieves data..

select * from (
select col1, col2, col3, col4....coln
from TABLE_ONE left outer join TABLE_TWO
-- some conditions and group by clause
order by 1 asc
)
where rownum <=1000;

Again if I use Column alias in the ORDER BY clause col1, the query won't retrieve data.

Also If I use ORDER BY 4 instead of ORDER BY 1, the query wont return data...

select * from (
select col1, col2, col3, col4....coln
from TABLE_ONE left outer join TABLE_TWO
-- some conditions and group by clause
order by 4 asc
)
where rownum <=1000;

The whole issue revolves around the inner ORDER BY Clause and external ROWNUM condition..If I eliminate any of the two, the query works fine...I am not sure if indexes have some role to play in it...

View 1 Replies View Related

SQL & PL/SQL :: Query Gets Slower By Adding Order By Clause

Apr 14, 2010

I have a query, running the query with order by and without orderby clasue casues big performance difference.

here without order by it takes 5 sec
.
SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,

[Code]....

here with order by it takes 2 min

SELECT
audit_number,
formatted_audit_number,
ag.sys_audit_id,
audit_begin_date,
audit_end_date,
auditee_name,
ein,

[Code]....

I have like 10,000 records.

View 16 Replies View Related

PL/SQL :: Order By In Union Clause In Date Column

Nov 3, 2012

Oracle version : 11.2.0.2 Linux EL6 server

I have a query like below:

select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union

[Code]...

Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.

But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).

I want every output in dd/mm/yyyy format so I need to use to_char function.

But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.

View 3 Replies View Related

SQL & PL/SQL :: View Not Having ORDER BY Clause In SELECT Statement?

Jun 13, 2010

currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :

Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.

The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.

but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.

View 5 Replies View Related

Forms :: Data Block Ignoring Order By Clause?

Mar 6, 2012

I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.

View 6 Replies View Related

Performance Tuning :: How To Tune Order By Clause Without Changing Sort Area

May 1, 2008

How to avoid sort operation by an order by clause without changing the sort area size.what hints or changes should be done in query so that order by clause work faster.

View 10 Replies View Related

Performance Tuning :: Index Usage In Order By Clause On Nullable Column

Jan 28, 2011

I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'

So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting

here are the steps

Initially I kept the column Nullable

SQL> create sequence s5;
Sequence created.

SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

SQL> create index i5 on t5(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit

SQL> alter session set events '10046 trace name context forever, level 12';

select *
from
t5 where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04

Now I modified the 'id' column to Not Null

SQL> alter table t5 modify id not null;

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y

14 rows selected.

select *
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45

select id
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89

Questions are

1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?

View 5 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 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 :: Order By With IN

May 24, 2010

Its a very small query

SELECT * from EMPLOY
WHERE fk_dept_id IN ( select id
from DEPARTMENT
ORDER BY END_DT DESC)

I cant use IN.

NOTE: Select * should be done from Employ only no joins and all.

View 10 Replies View Related

SQL & PL/SQL :: ORDER BY In A Particular Order

Jul 17, 2012

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have data like:-

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2011081CC
171350 2012041WA
171350 2012031WW
171350 2011081WW
171350 2011081CR
171350 2011081CA

The possible last two characters of the iss_nbr can be:-

WW, WR,WA,CR,CA,CC

And I want it to be ORDER BY as follows.

WR
WA
CR
CA
WW
CC

So for example, in above case, it should be

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2012041WA
171350 2012031WW
171350 2011081CR
171350 2011081CA
171350 2011081WW
171350 2011081CC

How can I do it while loading the table?

View 11 Replies View Related

SQL & PL/SQL :: Order By Row ID

Mar 20, 2012

I am having table without any primary key. In this table, only inserts and deletes are performed , no update operation.

Is it safe to use order by rowid on such a table ? Does by applying order rowid, is it possible to check order in which rows were inserted in this table ?

View 2 Replies View Related

Select Vs Order By

Nov 9, 2006

In a query which gets executed first:

Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?

What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?

select empno,ename,rownum from emp order by rownum desc;

View 1 Replies View Related

Tab Order On A Form

Feb 25, 2010

I am having trouble with tab order on a Form.

I rearranged the fields on a form, so I want the tab order to be different. I rearranged the fields in the object navigator to be in the order I want them to tab. I made sure the Previous Navigation Item and Next Navigation Item were set to null for all the fields.

Still it keeps the old tab order.

I also tried explicitly specifying the Previous Navigation Item and Next Navigation Item. Same thing, it kept the old tab order before I rearranged the fields. Previous Navigation Item and Next Navigation Item are both null.

There are no triggers on these fields. All fields are in the same block. I am using Oracle Form Builder version 9.

View 1 Replies View Related

SQL & PL/SQL :: Use Of Order By In Varchar2

Feb 21, 2011

I have a column named "col1" with datatype "varchar2(10)" and row wise entries like "1,1A, 2,3...,10,2A,..." like. I want to order it like "1, 1A ,2,2A, 2B,3... 10...".I tried it with to_number() but it gives me

1,10,11,2,....like that.

View 14 Replies View Related

SQL & PL/SQL :: How To Get Order By A Column

Jun 17, 2013

I am one table create tt1(A varchar2(6),B number(5));

insert data in this column

tt1
A B
---------------
F1 1
F365 2
F10 3
F65 4
F55 5

Q. how to get order by a column. i wan't to

tt1
A
--------------------
F1
F10
F35
F65
F365

Q. how to get order by a column , i wan't to

tt1
A
--------------------
F0001
F0010
F0035
F0065
F0365

View 13 Replies View Related

SQL & PL/SQL :: Order By Alphanumeric?

Aug 1, 2012

create table x(
sno varchar2(5)
);

insert into x values('A-1');
insert into x values('B-1');
insert into x values('B-2');
insert into x values('B-3');
insert into x values('1');
insert into x values('A-2');
insert into x values('2');
insert into x values('3');
insert into x values('A-4');
insert into x values('B-4');
insert into x values('C-4');
insert into x values('D-4');

SQL>select * from x;

SNO
-----
A-1
B-1
1
A-2
2
3
A-4
B-4
C-4
D-4
B-2
B-3

How can I select it ike this

1
A-1
A-2
A-4
B-1
2
B-2
3
B-3
B-4
C-4
D-4

View 15 Replies View Related

PL/SQL :: Order By Month Name

Jun 20, 2012

I've got a TRANSACTION table with about 4, 681 transactions going on over the course of a given year (this is a project for my DB class). I'm trying to create a query that will give the base revenue for each month in that year; so far I've come up with the following:

SELECT DISTINCT
CASE
WHEN EXTRACT(MONTH FROM transaction_date) = 1 THEN 'JAN' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 2 THEN 'FEB' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 3 THEN 'MAR' || ' ' || EXTRACT(YEAR FROM transaction_date)
WHEN EXTRACT(MONTH FROM transaction_date) = 4 THEN 'APR' || ' ' || EXTRACT(YEAR FROM transaction_date)
[code]....

The query returns twelve months, but they're all jumbled up. I tried extracting the month in the ORDER BY subclause

ORDER BY EXTRACT(MONTH FROM TO_DATE(month, 'MM YYYY'));

But I got an ORA-01866: the datetime class is invalid. I'm using Oracle 10g xe (outdated, I know). get the months to show up in order?

View 7 Replies View Related

PL/SQL :: Order By With Union?

Aug 24, 2012

I have the following sql

select htf.escape_sc(su.sukey) a, htf.escape_sc(su.sukey) b
from udm_su su, udm_lde lde
where su.ldeid = lde.ldeid
and su.sukey in (select su_generic
 
[code]...

View 3 Replies View Related

PL/SQL :: Order By On Datafile Name

Sep 4, 2013

I am using 11GR2 and looking to find out a way , where in I able to extract the data files name in such a manner , where it lists data file in order of mount points. say data1 first and then so on.. Snippet from my data files

output. /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data23/sans01.dbf 

should be something

like /db/ptmtrain/data1/system01.dbf/db/ptmtrain/data1/undotbs01.dbf/db/ptmtrain/data2/sysaux01.dbf/db/ptmtrain/data2/rbs03.dbf/db/ptmtrain/data2/rbs01.dbf/db/ptmtrain/data3/tools01.dbf/db/ptmtrain/data3/rbs02.dbf/db/ptmtrain/data23/sans01.dbf/db/ptmtrain/data24/users01.dbf/db/ptmtrain/data25/users02.dbf/db/ptmtrain/data26/users03.dbf  

View 16 Replies View Related

SQL & PL/SQL :: Order By For VARCHAR2

Sep 28, 2010

create table members(
memberID varchar2(7),
locationID number(5),
roomID varchar2(5)
)
/

[Code]....

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2006253 1 1-A
2006253 1 1-A
2006265 1 1-B
2006240 1 1-C
2005064 1 1-D
2007836 1 100
2007211 1 101
2007075 1 102
2007110 1 105

[Code]...

Function created.

SQL> select * from members
2 order by safe_to_number(roomID);

MEMBERI LOCATIONID ROOMI
------- ---------- -----
2007209 1 1
2007185 1 2
2007089 1 3
2007023 1 4

[Code]....

How can I sort the output to get the result like below

2007209 1 1
2006253 1 1-A
2006253 1 1-A

[Code]...

and so on...

View 13 Replies View Related

SQL & PL/SQL :: Order By Month

Jan 31, 2013

How I can see the order by employees,year wise and monthwise (month should start from january and so on..)

SELECT ENAME,TO_CHAR(HIREDATE,'yyyy') YEAR,TO_CHAR(HIREDATE,'Month') MONTH ,count(*) total
FROM EMP
GROUP BY TO_CHAR(HIREDATE,'yyyy') ,TO_CHAR(HIREDATE,'Month');

View 8 Replies View Related

PL/SQL :: How To Order By The Column

May 28, 2013

I have a varchar2 column which can hold numeric values, and also varchar2 values.

The following select returns only the records which have a number value in that column

select * from my_table
order by decode(
replace(translate(valoare,'1234567890.','00000000000'),'0',''), NULL,
valoare, 0 ) != 0;

But how can i order that column, so that first are the numbers (also ordered), and then the varchar2 columns (so all the rows of that table are selected, both varchar2 values and numbers in that column)? Is it possible?

View 11 Replies View Related







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