All Questions
64,086
questions
1208votes
14answers
1.9mviews
How do I limit the number of rows returned by an Oracle query after ordering?
Is there a way to make an Oracle query behave like it contains a MySQL limit clause?
In MySQL, I can do this:
select *
from sometable
order by name
limit 20,10
to get the 21st to the 30th rows (skip ...
- 14.6k
1166votes
24answers
2.3mviews
Get list of all tables in Oracle?
How do I query an Oracle database to display the names of all tables in it?
- 15.2k
628votes
35answers
642kviews
Fetch the row which has the Max value for a column
Table:
UserId, Value, Date.
I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL? (...
- 6,315
476votes
17answers
1.2mviews
How to create id with AUTO_INCREMENT on Oracle?
It appears that there is no concept of AUTO_INCREMENT in Oracle, up until and including version 11g.
How can I create a column that behaves like auto increment in Oracle 11g?
- 6,855
449votes
33answers
928kviews
How to select the nth row in a SQL database table?
I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native ...
- 19k
409votes
24answers
651kviews
Is there a combination of "LIKE" and "IN" in SQL?
In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question.
Further, I ...
- 14.8k
405votes
12answers
516kviews
Can a foreign key be NULL and/or duplicate?
Please clarify two things for me:
Can a Foreign key be NULL?
Can a Foreign key be duplicate?
As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to ...
- 20.6k
404votes
8answers
728kviews
What is the difference between Views and Materialized Views in Oracle?
What is the difference between Views and Materialized Views in Oracle?
- 76.8k
392votes
15answers
581kviews
Oracle: If Table Exists
I'm writing some migration scripts for an Oracle database, and was hoping Oracle had something similar to MySQL's IF EXISTS construct.
Specifically, whenever I want to drop a table in MySQL, I do ...
- 160k
348votes
15answers
907kviews
Update statement with inner join on Oracle
I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ...
- 3,887
326votes
10answers
711kviews
Best way to do multi-row insert in Oracle?
I'm looking for a good way to perform multi-row inserts into an Oracle 9 database. The following works in MySQL but doesn't seem to be supported in Oracle.
INSERT INTO TMP_DIM_EXCH_RT
(EXCH_WH_KEY, ...
- 4,543
318votes
11answers
478kviews
Oracle: how to UPSERT (update or insert into a table?)
The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data:
if table t has a row exists that has key X:
update t set mystuff......
- 281k
303votes
11answers
442kviews
Is there any boolean type in Oracle databases?
Is there any Boolean type in Oracle databases, similar to the BIT datatype in Ms SQL Server?
- 3,031
300votes
13answers
775kviews
How do I find duplicate values in a table in Oracle?
What's the simplest SQL statement that will return the duplicate values for a given column and the count of their occurrences in an Oracle database table?
For example: I have a JOBS table with the ...
- 12.7k
294votes
17answers
634kviews
How can I create a copy of an Oracle table without copying the data?
I know the statement:
create table xyz_new as select * from xyz;
Which copies the structure and the data, but what if I just want the structure?
- 12.7k