CODEWITHSUNDEEP
oracle-databaseoracle11g
sgp667
sgp667

Reputation: 1875

Return first row in each group from Oracle SQL

Hi I need to come up with a query that efficiently returns just one record per group(I might be thinking about it wrong) and stop searching for more records in that group as soon as it has found one record.

This is my table:

|col1 | col2|
|-----|-----|
| A   |  1  |
| A   |  2  |
| B   |  3  |
| B   |  4  |

I want to return. 

|col1 | col2|
|-----|-----|
| A   |  1  |
| B   |  3  |

Note that I don't actually care if in row one I have A,1 or A,2(same applies to second row). What I want is to get one record that has A in first column could be any record that matches that criteria, and similarly I want one record that has B in col1.

The closes that I know to getting this are two queries

SELECT col1, MIN(col2)
FROM tablename
GROUP BY col1

and the other:

SELECT *
FROM tablename
WHERE col1 = 'A' 
    AND ROWNUM = 1

First query is not good enough because it will try to find all records that have A in col1(in the actual table I'm looking at this means searching though millions of rows, and my indicies won't be of much help here). Second query will return just one value of col1 at a time, so I'd have to run it thousands of times to get all the records I need.

NOTE:

I did see similar question in here but the answers were focused on just getting the right query results, I my case issue is how long do I need to wait for these results.

Upvotes: 2

Views: 4329

Answers (2)

Karthik
Karthik

Reputation: 99

I feel the below query is returning the result quick.

 SELECT col1,col2 FROM (
        SELECT col1,col2, ROW_NUMBER () over (partition by col1 order by col2 asc) 
        minseq FROM tablename 
              --where rownum < 1000000000
        )
        where minseq = 1;

Normal query 

SELECT col1, MIN(col2)
FROM tablename
GROUP BY col1

took 1 min to fetch 500 records out of 100000000 records and this query took 0.03 seconds

Upvotes: 0

Sentinel
Sentinel

Reputation: 6449

Sounds like this is the query you are looking for:

select col1
     , min(col2) keep (dense_rank first order by rownum) col2
from tablename
group by col1;

Upvotes: 3

Related Questions