CODEWITHSUNDEEP
sqloracle-databaseselect
SaurabhD
SaurabhD

Reputation: 211

Distinct records on Select statement ORACLE-SQL

I have a table with possible duplicate records except one field i.e Field2 in below table. Field1 is Key to join Table.

Field1  Field2  Field3
  aa      T       qqq
  aa      F       qqq
  bb      F       eee
  cc      T       rrr

Now in Field2, if T and F both are there for same Field1, I want to select row with T as Field 2 values. In case only T or F is available I want that record. So output of the Select statement should be be

Field1  Field2  Field3
  aa      T      qqq
  bb      F      eee
  cc      T      rrr

Help me if there is efficient way to do it.

Upvotes: 0

Views: 98

Answers (5)

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

This is another one getting Col3 value as per Col2 and Col1 irrespective of Max() or Min() in columns

select col1, MAX(col2) as Col2 , 
(select top 1 col3 from Employee as E where E.Col1=Employee.Col1 and 
E.col2 = max(Employee.Col2) order by col3) as Col3 
from Employee 
group
by Col1

Find the solution in Fiddle http://sqlfiddle.com/#!6/77d2b/2

Upvotes: 0

Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

Try this

select col1,MAX(col2) as Col2,Max(Col3) as Col3 from Employee group by Col1

If you try to use distinct in one column by selecting multiple column you should use Max() or Min() to get the values

The problem you will find here is

col1  col2    col3
aa     T      qqq 
aa     F      zzz

In this case MAX(col3) will be zzz instead of qqq

In this case you have to choose between Max() or Min() as per your requirements.

Find your solution in fiddle http://sqlfiddle.com/#!6/257ff/3

Hope that helps

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

For your requirement I would use:

select 
   Field1,  
   max(Field2) as Field2,
   max(Field3) keep(dense_rank first order by Field2 desc) as Field3
from 
   table
group by 
   Field1;

The third column function gets the corresponding row to Field2.

Upvotes: 2

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

In this query you assign 0 if is 'T'

That way if there is a 'T' will always appear first.

The only assumption I made is 'T' and 'F' appear 1 or less for each Col1. Please tell me if that is correct

SQL FIDDLE DEMO

with cteEmp as (
    SELECT 
      Col1, Col2, Col3,
      ROW_NUMBER()
        OVER (PARTITION BY Col1 ORDER BY CASE 
                                            WHEN Col2='T' THEN 0 
                                            ELSE 1 
                                         END ) AS row_id    
    FROM Employee
)
SELECT E.* 
FROM Employee E INNER JOIN 
     cteEmp CE ON (E.Col1 = CE.Col1 AND 
                   E.Col2 = CE.Col2 AND
                   CE.row_id=1)

Upvotes: 0

APC
APC

Reputation: 146239

You can use an analytic function to assign a ranking number to values of Field2. 

select Field1,  Field2,  Field3 from (
    select Field1
            ,  Field2
            ,  Field3 
            , row_number() over (partition by Field1,  Field3 
                                 order by Field2 desc) rn
    from your_table
    )  
where rn = 1
/

This implementation assumes a reverse alphabetical sort will produce the correct order for your needs. If you have a more complicated scheme for prioritization you'll need to apply a transformation of some kind using CASE or DECODE.

Upvotes: 1

Related Questions