There is a table with such a structure

COL1 | COL2 | COL3 -----+------+----- a | d | t -----+------+----- d | t | a -----+------+----- a | t | d -----+------+----- m | n | l -----+------+----- l | m | n

Records that can be obtained from other records by permuting values ​​in columns should be displayed only once. In this case, the answer will be:

 COL1 | COL2 | COL3 -----+------+----- a | d | t -----+------+----- l | m | n

It is necessary to solve the problem using standard SQL

Tell me where to start and how to organize an algorithm.

  • and is there any other type of index column unique? - Ale_x
  • 1. Need only SQL? 2. What is SQL? 3. What for such a perversion? - alexlz
  • After laboratory work, the teacher asked an additional task. We have just passed the basics of sql. There are no unique columns. - ivan_i
  • I check the work request in oracle sql developer. First you need to arrange the rows in the table. Then select non-duplicate entries - ivan_i

4 answers 4

Here I got this perversion for MS SQL:

 select distinct max, middle, min from ( select (select MAX(v) from (values (col1), (col2), (col3)) as value(v)) as max ,( select * from ( select (case when col1 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col1 end) as m union select (case when col2 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col2 end) as m union select (case when col3 not in((select MAX(v) from (values (col1), (col2), (col3)) as value(v)),(select MIN(v) from (values (col1), (col2), (col3)) as value(v))) then col3 end) as m ) t where m is not null ) as middle ,(select MIN(v) from (values (col1), (col2), (col3)) as value(v)) as min from #t ) temp

Where #t is the source table.

  • DISTINCT need to apply only to max? - ivan_i
  • distinct in this context will apply to all fields - null
  • can we explain why the words values ​​are used here and what does MAX (v) mean? - ivan_i
  • Briefly on the example of select MAX (v) from (values ​​(col1), (col2), (col3)) as value (v) in the from section we use the designer of table values ​​(values ​​(col1), (col2), (col3)) , then we have to somehow name the created table - in the example I called it value, in parentheses we indicate the names of the columns - i.e. v. Well and in select we take a maximum on a column v. - null
  • In oracle sql developer, is it such a constructor to use? Are there other ways to sort values ​​in a string? - ivan_i

That's what I did - not quite right.

 SELECT DISTINCT max, midl, small FROM ( SELECT least(COL1, COL2, CoL3) as max,replace(replace(CONCAT(COL1, COL2, CoL3),greatest(COL1, COL2, CoL3),''),least(COL1, COL2, CoL3),'') as midl, greatest(COL1, COL2, CoL3) as small FROM test) as t1

If there is a unique column (id)
Here is the request

 SELECT COL1, COL2, COl3 FROM ( SELECT id, least( COL1, COL2, CoL3 ) AS max, replace( replace( CONCAT( COL1, COL2, CoL3 ) , greatest( COL1, COL2, CoL3 ) , '' ) , least( COL1, COL2, CoL3 ) , '' ) AS midl, greatest( COL1, COL2, CoL3 ) AS small FROM test ) AS t1, test WHERE test.id = t1.id GROUP BY max, midl, small

But if there are no more columns - the correct request

 SELECT test.COL1, test.COL2, test.COL3 FROM ( SELECT COL1, least( COL1, COL2, CoL3 ) AS max, replace( replace( CONCAT( COL1, COL2, CoL3 ) , greatest( COL1, COL2, CoL3 ) , '' ) , least( COL1, COL2, CoL3 ) , '' ) AS midl, greatest( COL1, COL2, CoL3 ) AS small FROM test ) AS t1, test WHERE test.COL1 = t1.COL1 GROUP BY max, midl, small
  • What is least (), replace () and greatest ()? Is it a function? - ivan_i
  • df sql function) - Ale_x

The algorithm is as follows:

  1. Sort the values ​​in the columns of CASE .
  2. Use DISTINCT.

    The solution to the problem turned out to be much easier. Maybe someone else will help)

     select distinct least(col1,col2,col3) as col1 ,greatest(col1,col2,col3) as col2 ,case when col1 not in (least(col1,col2,col3) ,greatest(col1,col2,col3)) then col1 when col2 not in (least(col1,col2,col3) ,greatest(col1,col2,col3)) then col2 else col3 end col3 from table1;