StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POselecting a distinct combination of 2 columns in SQL
text
Body
copied!<p>When i run a select after a number of joins on my table I have an output of 2 columns and I want to select a distinct combination of col1 and col2 for the rowset returned.</p> <p>the query that i run will be smthing like this:</p> <pre><code>select a.Col1,b.Col2 from a inner join b on b.Col4=a.Col3 </code></pre> <p>now the output will be somewhat like this</p> <pre><code>Col1 Col2 1 z 2 z 2 x 2 y 3 x 3 x 3 y 4 a 4 b 5 b 5 b 6 c 6 c 6 d </code></pre> <p>now I want the output should be something like follows </p> <pre><code>1 z 2 y 3 x 4 a 5 b 6 d </code></pre> <p>its ok if I pick the second column randomly as my query output is like a million rows and I really dnt think there will be a case where I will get Col1 and Col2 output to be same even if that is the case I can edit the value.. </p> <p>Can you please help me with the same.. I think basically the col3 needs to be a row number i guess and then i need to selct two cols bases on a random row number.. I dont know how do i transalte this to SQL</p> <p>consider the case 1a 1b 1c 1d 1e 2a 2b 2c 2d 2e now group by will give me all these results where as i want 1a and 2d or 1a and 2b. any such combination.</p> <p>OK let me explain what im expecting:</p> <pre><code>with rs as( select a.Col1,b.Col2,rownumber() as rowNumber from a inner join b on b.Col4=a.Col3) select rs.Col1,rs.Col2 from rs where rs.rowNumber=Round( Rand() *100) </code></pre> <p>now I am not sure how do i get the rownumber or the random working correctly!!</p> <p>Thanks in advance.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload