Oracle Table Sort Order
Jul 16, 2010
I know the only way to guarantee a specific sort order result when querying table is by using the order by clause. However, I have an issue where I do not have access to the code for the web user interface of a very lightly used interface (has two users). In that user interface is a drop down box that is populated by a table - one table. The drop down box is populated by the query "select [column name] from [table name]". Right now there are 400+ rows in that table total, so it's small but not having the items ordered is a pain.
I would like to alter something on the db side so the result of "select [column name] from [table name]" is an ordering by the column descending. I don't want the sort order to be the same for all queries (including joins and all) just want to control the order for that one query. It is Oracle9i.
View 1 Replies
ADVERTISEMENT
Oct 31, 2006
I have data that i am sorting, the data is mostly numeric (format of XXX-1234). is there any way to have my query sort xxx-1000 AFTER xxx-999? right now i am thinking i will have to create a separate sort order column on my table.
View 2 Replies
View Related
Dec 12, 2010
I have a name field like below; i need a query to display the name field in alphabetical order like in dictionary.
Emp Name
--------
Sam
John
Noel
Alen
Saaem
output would be
---------------
Alen
John
Noel
Saaem
Sam
View 5 Replies
View Related
Oct 31, 2012
Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.
select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END
Note: DESC or ASC is dynamic coming from java-code.
View 3 Replies
View Related
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
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
Jul 3, 2011
how to sort the names in table without using group by command?.
View 3 Replies
View Related
Mar 15, 2013
I know how to select the last N sets of rows, using DENSE_RANK - where multiple rows have the same timestamp but I want to only select those rows which do NOT have the top 2 unique timestamps.
i.e.:
SELECT *
FROM ( SELECT DENSE_RANK() OVER (ORDER BY myTimestamp DESC) DENSE_RANK, HISTORYID, USER_ID, myTimestamp, STATUS, FROM TXN_HIST)
WHERE DENSE_RANK > 2 order by myTimestamp DESC, HISTORYID, USER_ID;
But how do I DELETE these same rows?
View 3 Replies
View Related
Apr 18, 2013
joining the Order table with the Asset table or the Order table with CX_ACTION table to Can i get reference on this .
select x.x_msisdn,
x.x_product_name,
x.x_action,
x.x_status_outcome,
a.recovered_dt,
x.x_amount,
[code].....
View 1 Replies
View Related
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
Jun 25, 2012
I have the following table:
CREATE TABLE test_A(member_id NUMBER(2) PRIMARY KEY, MEMBER_name VARCHAR2(20), MEMBER_parent NUMBER(2) DEFAULT NULL);
INSERT ALL
INTO test_A VALUES (1, 'mem1', NULL)
INTO test_A VALUES (2, 'mem2', NULL)
INTO test_A VALUES (3, 'mem3', NULL)
INTO test_A VALUES (4, 'mem4', 1)
INTO test_A VALUES (5, 'mem5', 1)
[code]....
As the actual data is huge, I need to know the best (least expensive) way to select each parent and then all its direct child ordered by member_id the output should look like:
1mem1(null)
4mem41
5mem51
6mem61
2mem2(null)
7mem72
8mem82
10mem102
3mem3(null)
9mem93
11mem113
View 3 Replies
View Related
Mar 28, 2012
How to find the tables starting with smallest size and vice versa in schema level and database level?
View 4 Replies
View Related
Jul 13, 2010
I would like to reorganize a table inorder of primary key but I'm not sure if I'm expecting the right thing from dbms_redefinition package.
I am working on oracle 9i 9.2.0.8
I have the following table :
SELECT * from SCOTT.TESTTABLE
ID REF
---------- ----------
1 FF
2 BB
3 CC
4 DD
8 EE
6 ZZ
7 YY
5 GG
when I use the CODEexec dbms_redefinition.start_redef_table('SCOTT', 'TESTTABLE', 'TESTTABLE2', 'id id, ref ref', dbms_redefinition.cons_use_pk);
Would the newly created scott.testtable be created in order of primary key (ID) thus a select * from scott.testtable will give me an ordered result?
When I do the test, the table before and after the redefinition is exactly the same so why use the CONS_USE_PK if it doesn't order the table by primary key?
View 5 Replies
View Related
Apr 23, 2012
i need to write a query in order to receive a new table.
select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys
[code].......
View 21 Replies
View Related
Sep 4, 2010
I have a table as below:-
CREATE TABLE TEST_EMP_WIP
(
EMP VARCHAR2(20 BYTE),
WIP NUMBER,
MAX_SEQUENCE NUMBER
)
[Code]....
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 4
011 1 5
I have to find the next EMP whose MAX_SQUENCE needs to be updated with a sequence. In this case the next would be '010' I fetched the EMP who falls after the max value of the column max_sequence in order of EMPCODE.
select emp_next from (
SELECT emp,max_sequence, LEAD(emp, 1, 0) OVER (ORDER BY emp) AS emp_next
from test_emp_wip where wip > 0 )
where max_sequence=(select max(max_sequence) from test_emp_wip where wip > 0)
EMP_NEXT
010
After sometime, the record would look like :-
EMP WIP MAX_SEQUENCE RANK
002 2 2 1
003 1 4 2
005 3 5 3
010 1 6 4
011 1 7 5
Now the next update should be done to the EMP '002' in cyclic manner.
tell me a way to do this. I can insert new columns if needed in table for calculation.
View 5 Replies
View Related
Nov 23, 2012
I have a table (emp) with three columns in ORACLE:
COMPANY OFFICE EMP
------------------------
9999 00001 emp1
9999 00001 emp2
9999 00002 emp3
9999 00002 emp4
[code]...
I want to get value in EMP column with following statement:
-------------
SELECT
EMP
FROM
emp
ORDER BY COMPANY (But 9999 is first), OFFICE (But 99999 is first).
-------------
I don't know how to do.
View 2 Replies
View Related
Nov 30, 2009
my simplified database schema is:
Order
OrderID
Status
OrderItems
OrderID
EAN
Amount
Store
EAN
Amount
now,I need trigger that will on updating table Order and changing status to "GoodsReceived" increase amounts in Store according to values in OrderItems.
create or replace trigger order_received_trigger
before update
on Order
for each row
begin
if (:old.status=4 and :new.status=1) then
/*
select ean, sum(amount)
from OrderItems
where OrderID=:old.OrderID
group by ean;
*/
end if;
end;
but now i dont know how to apply that select on table Store.
View 2 Replies
View Related
Oct 30, 2011
There are 2 setup of Oracle DB 9.2.0.8.0. There is a table T1 in both (one that is working 6.5 million rows and the other that is not working having 6.7 million rows). while doing - select * from t1 order by c1 desc; one is using index i1 and other is going for full table access.
View 1 Replies
View Related
Aug 31, 2010
I have migrated from Oracle 8i (8.1.7) to Oracle 10g, but when I execute a query in 8i without any order by clause, I get a result in ascending order. The same query when executed in 10g gives a result which is not ordered. How to get an order result in 10g. There are many forms and reports which use lov which are not ordered. Can I set the ordering at the database, so that I do not have to alter all the forms and reports.
I have also migrated my forms from 5 to 6, but the combo box in some forms in 6i do not appear at run time. How I can solve this problem. I have attached an forms5 .fmb file/.
View 7 Replies
View Related
Apr 11, 2013
I have two block....both are the control block.. in first block i select the date and in second block the data of that date is populated.but the data is populated using cursor in when-button-pressed trigger of that first block button.in cursor the data is selected and placed in field of detail block using into clause.... each field and one item of detail block is srno which is create in post-query of detail block using :sysyem.trigger_record.
Now i want after populated the detail block the data is sorted desc one of the field of the detail block..Can this possible using set_block_property() of block although the block is control block if yes where i should do this?
View 7 Replies
View Related
Nov 30, 2010
Is there a way to short a Matrix report? The query shorts correctly in PLSQL but not in the generated report file.
There are columns like Date,Device,Operator Id etc and the report should be able to short depending on the requirement. For example,
it should be able to short by device, or by Date or both. I tried sorting in the 'Break order' but doesn't come out as required.
View 6 Replies
View Related
Nov 7, 2012
We have Oracle DB 11g.I want to develop a small application for internal use the company.I have used Oracle Application Express workspace provided by Oracle and I thought of using Oracle Apex is the right tool for our internal reporting.
But I am not sure what is needed in order to start developing the application in-house.What should I check with the DBA team in order to find out if I can use Oracle APEX in the organization.Is there any prerequisites, web Server, etc that is needed in order to use Oracle Apex?
View 4 Replies
View Related
Nov 23, 2008
I have the following three tables:
Buyer:
BuyerID
Name
Trans:
TransID
BuyerID
Trans_Item:
Qty
Price
BuyerID
TransID
I need to figure what buyer has bought the most things. I have a function already determines the amount each buyer has bought.
So that is done. I need to order this by buyerid. How do I sort something like that? ORDER BY and GROUP BY do not work.
View 2 Replies
View Related
Mar 10, 2010
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
View 8 Replies
View Related
Jan 20, 2012
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Sharma 01-Jul-1987
Avinash Pandey 03-Feb-1988
Ankit Gupta 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Sikha Batham 17-Apr-1988
Now if I give parameters-
first_name='Manu'
last_name='Batham'
dob='02-Feb-1988'
then my result should be like below-
result-
first_name last_name dob
---------- --------- ----
Manu Batham 02-Feb-1988
Manu Aggrawal 02-Feb-1988
Manu Batham 20-Jan-1985
Manu Sharma 01-Jul-1987
Ankit Gupta 02-Feb-1988
Sikha Batham 17-Apr-1988
My result is based on the approach-
if matched first_name, last_name, dob --> 1st prefrence in order
if matched first_name, dob --> 2nd prefrence in order
if matched first_name, last_name --> 3rd prefrence in order
if matched last_name, dob --> 4th prefrence in order
if matched first_name --> 5th prefrence in order
if matched last_name --> 6th prefrence in order
if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where
first_name='Manu' and
last_name='Batham' and
dob=to_date('02/02/1988','dd/mm/yyyy')
union
Select a,b,c,2 from test Where
[code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
View 19 Replies
View Related
Dec 11, 2008
I have a field called fullname that outputs records with fullname of people.
Here is what I have when I do this sql:
Select fullname from tableOne;
John Jones
Bill Aronsen
Sam Baker
George Williams
Dave Smith
I would like to sort in order of last name but cant figure out how to do the sql:
Bill Aronsen
Sam Baker
John Jones
Dave Smith
George Williams
View 1 Replies
View Related
Jun 16, 2010
how does sorting on multiple columns work
suppose my query is
select * from person order by first_name desc
and sys_person_id asc
this query works , but is this write way to sort on multiple column ?
View 12 Replies
View Related
Nov 20, 2008
I have an urgent request which is pending with the following problem.
Problem :
I have a table which contains data of various datatypes like alphanumeric,varchar and number.
Now my query is " how to sort the data of the table using alphanumeric field"
How to select the data in a required(MyRequirement) sort order.
Data
============
12.4PI1
12.4
12.2
12.4T
12.3PI1
[Code]....
afterSorting(which I am getting Now)
============
12.2
12.3PI1
12.4
12.4PI1
12.4PI10
12.4PI11
[Code]...
MyRequirement
===============
12.2
12.3PI1
12.4
12.4PI1
12.4PI2
12.4PI3
12.4PI10
[Code]..
Means it has to sort the data order by lefthand side of PI and also righthand side of PI.
Pls check the attachment if you are not getting the above data in correct order.
View 22 Replies
View Related
Jun 22, 2011
I want a trigger i have made a software abut school system i need a trigger to sort out the positions...if total number like 100,99,98 than in positions column 1,2,3 but if total marks same like 100,100 in position column shows 1,2..i need if the marks are same than in position column also same like if marks 100,100 in position column shows 1,1
View 3 Replies
View Related
Oct 17, 2012
Query result gives out put like in the following order
CITY NAME
CHENNAI
DELHI
LONDON
RIO DE GENARO
How to get output in the following sort order
LONDON
CHENNAI
DELHI
RIO DE GENARO
i dont want to hardcode the column values ..it will be great if i get option to generate dynamically.
View 9 Replies
View Related
