All Questions
25 questions
0
votes
2
answers
31
views
Ordering by IN clause order
Suppose I want the order of output records match the order in the IN clause
SELECT * FROM (
SELECT 1 AS id, 'one' AS text FROM DUAL
UNION ALL
SELECT 2 AS id, 'two' AS text FROM DUAL
) t WHERE t.id IN (...
- 169
0
votes
1
answer
58
views
Oracle MAX gives different result than ORDER BY desc
When trying to sort some hexadecimal values I've got a different results for MAX() aggregation and ORDER BY desc sorting:
with some_data as
(
select '123A55' as hex
from dual
union all
...
- 110
0
votes
1
answer
44
views
How to query in the given order
select * from table where options=(10,223,43,1,23,54,323,32)
But the result is not coming in the given order of options. Options need to be changed frequently.
- 1
0
votes
3
answers
234
views
Does Oracle guarantee final output row order when it is set in the inline view
This query is based on 2 tables where some rows in PerfData1 have multiple child records. Rows are not duplicate but some ids are. I need to take only 1 of such rows and this is also a paging query, ...
- 216
2
votes
3
answers
140
views
Ordering streets by their names and polysyllabic numbers in SQL query
In Oracle DB there is a table with some data:
LAG
HNR
STREET
00020
44
Aachener Straße
00020
44/2
Aachener Straße
00020
44/1
Aachener Straße
00020
46
Aachener Straße
00020
46/6
Aachener Straße
...
- 177
0
votes
1
answer
131
views
Creating a table with Rank ordering in Oracle [closed]
I am trying to create a table in Oracle as follow:
create table hops.t_distribution AS
(select
UID,
CONTACT_EMAIL,
PRODUCT_ID,
rank() over(Partition by UID order by to_number(PRODUCT_ID)) as ...
- 1
0
votes
3
answers
159
views
What order does Oracle's EXIST condition compare the sub-query results?
I know EXIST works better than IN for larger sub-queries because it checks each sub-query record individually and stops once a comparison is found.
Let's say I can order the sub-query in a way to ...
ray_haan
1
vote
1
answer
2k
views
Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | ...
- 233
0
votes
1
answer
4k
views
oracle sql: How to not select select duplicate records from table
Can any help me to fix the below sql query
SELECT DISTINCT ORDER_NUMBER, FLAG
FROM TABLE
Below was the result
Order_number FLAG
LP-13288 false
LP-13288 true
LP-13292 false
LP-13290 ...
- 129
0
votes
1
answer
2k
views
Unable to apply order by clause on distinct select in Oracle
The query is:
select distinct( Name )
from Students
where marks>75
order by Substr(Name,-3,3),ID asc;
This shows an error message:
order by Substr(Name,-3,3),ID asc
*
ERROR at line 4:
ORA-...
- 1
2
votes
1
answer
1k
views
Oracle pagination with order by field not unique performance
Usually, we will write pagination SQL like this if order by field is unique:
SELECT *
FROM (
SELECT XX.*, ROWNUM AS RN
FROM (
SELECT *
FROM T_LOG
WHERE OP_TYPE = 'Q' ...
- 23
13
votes
4
answers
6k
views
Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?
I stumbled upon this question on a Twitter conversation with Lukas Eder.
Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using ...
- 907
2
votes
2
answers
11k
views
How can I improve performance of Order By clause in Oracle
I have a table called UAVT_BINA in oracle database and it have 28897352 rows. When I write:
select * from UAVT_BINA
the DB responds to me in 0.1 second. But when I write
select * from UAVT_BINA ...
-1
votes
1
answer
3k
views
Does Merge take Order by into account when updating
I have to merge a subquery result into table A. Here's the basic merge statement:
MERGE INTO A
USING (SELECT CUSTOMER_ID,LAST_LOGIN_DATE) B ON A.CUSTOMER_ID=B.CUSTOMER_ID
WHEN MATCHED THEN
UPDATE ...
0
votes
1
answer
65
views
Moving `order by` Significantly Improves Execution Time
I provide a simply query that, I think, resembles my real query.
I'm using Oracle.
Given:
create table main_table(a NUMBER, b VARCHAR2(10))
create table rates_table(a NUMBER, rate NUMBER)
create ...
- 559
-1
votes
1
answer
308
views
Random Order of Numbers in Select Statement [duplicate]
In the case where you've specified the following list of numbers, which are in random order, will the results be in the same order?
where item in
(
371355,
371476,
371629,
370556,
...
- 7
0
votes
3
answers
6k
views
Oracle union default sort is weird [closed]
As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't.
I found out that if there's no order by clause, Oracle sorts the merged dataset by the first ...
- 111
6
votes
3
answers
15k
views
Does an order by clause have a negative effect on performance?
I couldn't find the answer by googling.
I only over heard a senior dev telling a junior dev that using an order by clause has a negative effect on performance and he shouldn't use it(Oracle).
In ...
- 439
-1
votes
1
answer
69
views
Setting An Order For Specific Fields Using SELECT and ORDER BY
So I'm writing a query to show the hire_date, job_id and last name where the last name= 'Matos' and 'Taylor and the hire_date is in ascending order.
When executing this code:
SELECT last_name, ...
0
votes
3
answers
43
views
I want a select to give me id's that are not in a consecutively order
I have an Oracle db with a table like this:
+----+-------+
| ID | INDEX |
+----+-------+
| 1 | 0 |
| 1 | 1 |
| 2 | 4 |
| 2 | 5 |
| 2 | 6 |
| 3 | 0 |
| 3 | 1 |
| 3 |...
- 349
1
vote
1
answer
311
views
How to sort query filename output like Windows explorer?
i would like to order my query output the way Windows Explorer does.
First special characters like _, then the numbers, and the letters last.
_ttt.bmp
0.gif
bbb.png
- 11
1
vote
1
answer
2k
views
Oracle - WHY do an ORDER BY for INSERTs AS SELECT?
I have an application that was migrated from MSSQL to Oracle 11G.
It needs re-design but that's outside the scope of the project.
In short, the application gets legacy data, builds a parent table ...
- 135
3
votes
1
answer
81
views
Special ordering
I have a hierarchical query but I don't manage to order it as I want.
I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly.
This a ...
- 31
1
vote
2
answers
118
views
Special Oracle Order
I have a table (emp) with two columns in ORACLE:
COMPANY OFFICE EMP
------------------------
9999 00001 emp1
9999 00001 emp2
9999 00002 emp3
9999 00002 emp4
...
- 53
8
votes
5
answers
13k
views
Oracle sort varchar2 column with special characters last
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
- 181