Solved

Selecting the first record in a subquery in Oracle

Posted on 2014-04-10
2
1,775 Views
Last Modified: 2014-04-10
Experts,

I want to read a table that has multiple records with the same ID number to get a value.  Is there a way in Oracle to select the first record only that meets the criteria?

I would like to use a subquery and I don't want to get the error multiple rows returned.
0
Comment
Question by:morinia
2 Comments
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 250 total points
ID: 39992273
I think you are talking to select distinct records from a table which has duplicate values

select * FROM your_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM your_table
GROUP BY column1, column2, column3)

where column1,column2,column3 are the duplicate columns, this will list you non-duplicate rows
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 250 total points
ID: 39992283
Another method

select * from
(
  select * from table where some_condition=true
) where rownum < 2;

>>I would like to use a subquery and I don't want to get the error multiple rows returned.

I am assuming you are trying to use this as inline view.
ex.
select a, b, (select statement) X from table etc
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question