CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Dortimer
Dortimer

Reputation: 617

Pull multiple records from different groups in Oracle based off value in different column

It might sound a little convoluted, so I'll try to keep it brief. Essentially I need to run a query that groups the results based on COL1, and from there it looks at the maximum value in COL2. Once it determines what is the highest value in COL2, it should then look at the value in COL3, and then pull every record in the group that has the same value. There are other columns in the table that I need values from, but they aren't used in the query logic. 

SOME_TABLE

  COL1    COL2     COL3
1 12345   100      A
2 12345   (null)   A
3 12345   50       B
4 12346   0        A
5 12346   (null)   A
6 12346   100      B
7 12346   20       B

So in this example for rows with COL1 value of 12345I would want rows 1 and 2 (it just needs a single instance of the highest value, it doesn't matter what the rest of the rows' values are). For COL1 with the value of 12346 I need rows with B in COL3.

Here's sample output in a little bit more of a readable format:

  COL1    COL2     COL3
1 12345   100      A
2 12345   (null)   A
6 12346   100      B
7 12346   20       B

I have tried numerous different queries and permutations of the queries, but with no luck. I'm not sure how constructive it would be to post a bunch of queries that don't work. The biggest two approaches I've tried include a regular GROUP BY statement, and then also PARTITION BY on COL1.

I should also note that I tried implementing this on the C# side of the application, and also using looping in the SQL script but results were either mixed or the performance was incredibly poor.

I haven't found anything useful across SO or in Oracle's "documentation."

Upvotes: 1

Views: 54

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You want all the rows where the col3 values matches the max for col1. A correlated subquery seems like the most natural way to express this:

select t.*
from t
where t.col3 = (select max(t2.col3) keep (dense_rank first order by col2 desc)
                from t t2
                where t2.col1 = t.col1
               );

The long keep expression is just saying "keep the first value of col3 for the biggest value of col2.

Upvotes: 1

alexherm
alexherm

Reputation: 1362

Sample expected output might help. Try this:

    select col1, col3, max(col3)
    from table
    group by col1, col3

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You could use windowed functions:

WITH cte AS (SELECT t.*, MAX(col2) OVER(PARTITION BY col1) m FROM t)
SELECT *
FROM t
WHERE (COL1,COL3) IN (SELECT col1, col3 FROM cte WHERE col2 = m);

db<>fiddle demo

Output:

+-----+--------+-------+------+
| ID  | COL1   | COL2  | COL3 |
+-----+--------+-------+------+
|  2  | 12345  |       | A    |
|  1  | 12345  |  100  | A    |
|  7  | 12346  |   20  | B    |
|  6  | 12346  |  100  | B    |
+-----+--------+-------+------+

Upvotes: 0

Related Questions