Oracle Select Distinct Return Many Columns And Where
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
Solution 1:
If selecting just those 2 columns (name and col1) is sufficent you can use:
selectdistinct 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 = 2groupby (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 (partitionby x.name orderby x.name, x.col1, x.col2, x.col3) rnk
from table_name x
where x.col1 =2) xx
where xx.rnk =1;
Solution 2:
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 = 2groupby 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) )
Post a Comment for "Oracle Select Distinct Return Many Columns And Where"