SQL Group BY COLUMN Choose specific rows

Question

I have been looking for a long time but couldn't find any interesting answers.

Here is my problem, let's say I have a table with 2 columns and multiple rows :

column1  |   column2
         |
BKZ-54   |   N/A
BKZ-54   |   OPVE7
BKZ-54   |   OXP5V
ARF-47   |   RB5FR 
ARF-47   |   N/A
ARF-47   |   V7FTS 

Expected response after SQL Request :

column1  |   column2
BKZ-54   |   OPVE7
ARF-47   |   RB5FR

Problem is that if I use GROUP BY column1, random values of column2 will be chosen and I want to exclude those with N/A.

If I use GROUP BY column1 and WHERE column2 != 'N/A', then rows are inexistent in the response.

I want the SQL Request to group by column1 and get a random value corresponding in column2 but different from 'N/A'.

The only way I see is to make 2 SQL Request but I'm looking for a one time request. Thanks for your answers.

Answer 1

SQL Server example. You eliminate the 'N/A', but the real trick is to get a top 1 row per Column1 value. I'm accomplishing this by generating row numbers that are partitioned by Column1 and then only accepting where Row=1. NEWID() will cause them to be randomly ordered. Run the inner SELECT statement separately so you can see what it is doing.

DECLARE @table TABLE (column1 VARCHAR(200),column2 VARCHAR(200))
INSERT INTO @Table SELECT 'BKZ-54','N/A'
INSERT INTO @Table SELECT 'BKZ-54','OPVE7'
INSERT INTO @Table SELECT 'BKZ-54','OXP5V'
INSERT INTO @Table SELECT 'ARF-47','RB5FR'
INSERT INTO @Table SELECT 'ARF-47','N/A'
INSERT INTO @Table SELECT 'ARF-47','V7FTS'

SELECT
    Column1,Column2
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY NEWID() ASC) AS [Row]
    FROM @table
    WHERE column2<>'N/A'
) AS OrderedData
WHERE OrderedData.[Row]=1

Answer 2

I'm unsure if you want a truly "random" value, or if any value will do and it just doesn't matter which. If it's the latter use an aggregate like MAX() for choosing the value:

SELECT column1, MAX(column2)
FROM yourTable
WHERE column2 <> 'N/A'
GROUP BY column1