SELECT DISTINCT COLUMN pair in SQL

Question

Consider

create table pairs ( number a, number b ) 

If the data

  1.4
 4.1
 2,4
 2,4
 3.2
 3.2
 2,3
 5.1
 Etc.

I get it

  1.4
 4.1
 2,4
 3.2
 2,3
 5.1

Which query gives me the different values ​​that the column of b has. So I can see

  1.4
 5.1
 2,4
 3.2

only

I do not want the value in column a to be present in column b. Please, help.

I need to select different values ​​on either side of the column. eg. if (1,2) is present, then (2,1) should not be present

Answer 1

You did not specify your DBMS, but it works in many DBMSs (the least and greatest , unfortunately, are not part of the SQL standard)

 select distinct least(a,b), greatest(a,b) from pairs 

Answer 2

 select distinct p1.a from pairs inner join pairs p2 on p1.a=p2.b 

Answer 3

You can massage your data and first put a smaller value:

 SELECT CASE WHEN a < b THEN a ELSE b END a, CASE WHEN a < b THEN b ELSE a END b FROM pairs 

Then you can choose a different one from this:

 SELECT DISTINCT a, b FROM ( SELECT CASE WHEN a < b THEN a ELSE b END a, CASE WHEN a < b THEN b ELSE a END b FROM pairs ) x 

---

Alternatively, if there is no difference in value between a and b , you can enforce a constraint that causes a always be less than or equal to b while manipulating the data in the path. Then you can just do a simple SELECT DISTINCT .

Answer 4

You can try this query to get all the different values ​​in column a

 SELECT distinct(a) FROM pairs WHERE not exists(select b from pairs where a=b) 

Answer 5

It looks like you need an extended version of DISTINCT.

You can do this (in SQL Server) ... via CURSOR.

Load the cursor from your table. Scroll the cursor, map the template, and save what you need by filling in (INSERT) into the working (or temporary) table in each iteration of the cursor.

Choosing from this summary table will present your results. Remove the desktop, or the temp table will be reset at the end of the session.

You can also put this logic in a stored procedure.

Note. Cursors are intense in memory, especially if they are not used properly. But it is very convenient for nontrivial or complex logic.