ORACLE Select Distinct return many columns and where

Question

I have a table which looks like this:

NAME    Col1     Col2    Col3
Tim     1        2       3
Tim     1        1       2
Tim     2        1       2
Dan     1        2       3
Dan     2        2       1
Dan     2        1       3

Im trying to make a SELECT command which results in this:

NAME    Col1     Col2    Col3
Tim     2        1       2
Dan     2        2       1

So I want distinct Names but select all four columns and where Col1 = 2. I used the solution here to get the distinct part working: SQL/mysql - Select distinct/UNIQUE but return all columns?

But when I add add Col1 = '2' to the WHERE part of the select statement is does not return all names where Col1 is 2 because i think it looks at a different one first which is not 1 so takes that result.

Hope I made sense and someone can help. Was hard to explain and come up with a good title. Thanks!

You have two columns which have col1 = 2 and Name = 'Dan' but you did not specify how you want to choose which one to display... Dan 2 2 1 Dan 2 1 3 — het2cz, Feb 14 '14 at 11:59
Don't care which one as long as it tells me there is a Dan with Col1 = 2 instead of getting the one where col1 = 1 and then does not show Dan. — Safinn, Feb 14 '14 at 12:00
Then dont select all the rows, just `select name,col1 from table1 where col1=2' — G one, Feb 14 '14 at 12:02
that will give me 2 dans because there are 2 which have col1 = 2. I want just 1. — Safinn, Feb 14 '14 at 12:03
the group by will still give me 2 dans because i have to group by all selected columns (oracle). — Safinn, Feb 14 '14 at 12:07

score 1 · Answer 1 · answered Feb 14 '14 at 12:38

If selecting just those 2 columns (name and col1) is sufficent you can use:

select 
    distinct x.name, x.col1
  from table_name x
  where x.col1 = 2;

or

select 
    x.name, x.col1
  from table_name x
  where x.col1 = 2
  group by (x.name, x.col1);

In case you need all values but you dont mind which one for the multiple records fulfilling your criteria yu get (e.g. Dan 2 2 1 or Dan 2 1 3), you can use this (it will record first of those records based on order by criteria):

select xx.name, xx.col1, xx.col2, xx.col3
  from (select 
      x.name, x.col1, x.col2, x.col3, dense_rank() over (partition by x.name order by x.name, x.col1, x.col2, x.col3) rnk
    from table_name x
    where x.col1 = 2) xx
  where xx.rnk = 1;

score 0 · Accepted Answer · edited Feb 14 '14 at 23:00

0

As I understand this query should solve your problem:

select distinct a.* from temp_ids a join

(select name, max(col1 || ' ' || col2  || ' ' || col3) id
from temp_ids
where col1 = 2
group by name
) b

on (a.name = b.name and (col1 || ' ' || col2  || ' ' || col3) = b.id)
;

Of cause, better to use unique record id instead concatenation, but generated id is possible. Be sure that function for id returns unique value for each combination of columns (in this case used (col1 || ' ' || col2 || ' ' || col3) )

edited Feb 14 '14 at 23:00

joeldixon66

950
8
13

answered Feb 14 '14 at 22:37

AppLend

1,644
1
15
12

1

Yes I have used something very similar but simpler. `SELECT name, MAX(col1), MAX(col2), MAX(col2) FROM table WHERE col2 = '2' GROUP BY name ORDER by name` – Safinn Feb 17 '14 at 08:27
Yes, the unique row identifier is required only if you need to select values from the same row – AppLend Feb 17 '14 at 09:28

ORACLE Select Distinct return many columns and where

2 Answers2

Linked