Select at least one from each category, but not more than one, and no duplicates of the other column

Question

For a table such as:

 Id | Vehicle Type | Manufacturer
 --------------------------------
 1  | Car          | SpaceCo
 2  | Car          | NeatCarsInc
 3  | Car          | NeatCarsInc
 4  | Spaceship    | SpaceCo
 5  | Spaceship    | NeatCarsInc
 6  | Spaceship    | SpaceCo
 7  | Boat         | WeMakeBoats
 8  | Boat         | SpaceCo
 9  | Boat         | NeatCarsInc

I need to write a query to meet the following:

• Req # 1 Make sure that this type of vehicle is not repeated in the result set
• Req # 2 Make sure that this manufacturer does not repeat in the result set
• Req # 3 If there are any entries for the type of vehicle, then for each type of vehicle there should be an output line (within the limits of restrictions No. 1 and No. 2)
• Note: each manufacturer may appear 0-1 times in the result set, and there is no need to try to select each manufacturer

, (, ).

:

1  | Car          | SpaceCo
5  | Spaceship    | NeatCarsInc      
7  | Boat         | WeMakeBoats  

:

2  | Car          | NeatCarsInc
4  | Spaceship    | SpaceCo      
7  | Boat         | WeMakeBoats  

( , № 3):

9  | Boat         | NeatCarsInc
6  | Spaceship    | SpaceCo   

, , , , .

, , : ?, (1) (2 ) .

---

, , SQL, Knapsack...

, , :

• , .
• , .

Req # 3, .. :

:

1  | Car          | SpaceCo
7  | Boat         | WeMakeBoats   

:

6  | Spaceship    | SpaceCo
9  | Boat         | NeatCarsInc  

():

2  | Car          | NeatCarsInc
6  | Spaceship    | SpaceCo
7  | Boat         | WeMakeBoats  

SQL :

   SELECT Id, VehicleType, Manufacturer
   FROM
   (
     SELECT 
       RANK() OVER (PARTITION BY Manufacturer ORDER BY [Id] ASC) ManufacturerRank,
       Id,
       VehicleType,
       Manufacturer
     FROM
     (
       SELECT 
         RANK() OVER (PARTITION BY VehicleType ORDER BY [Id] ASC) VehicleRank,
         Id,
         VehicleType,
         Manufacturer
       FROM
         Vehicles
     ) RankedPerVehicleType
     WHERE VehicleRank = 1
   ) RankedPerManufacturer
   WHERE ManufacturerRank = 1

Answer 1

ID,

CREATE TABLE #TAB (Id INT, Vehicle_Type VARCHAR(250), Manufacturer VARCHAR(250))

INSERT INTO #TAB
SELECT 1,'Car','SpaceCo'
UNION ALL
SELECT 2,'Car','NeatCarsInc'
UNION ALL
SELECT 3,'Car','NeatCarsInc'
UNION ALL
SELECT 4,'Spaceship','SpaceCo'
UNION ALL
SELECT 5,'Spaceship','NeatCarsInc'
UNION ALL
SELECT 6,'Spaceship','SpaceCo'
UNION ALL
SELECT 7,'Boat','WeMakeBoats'
UNION ALL
SELECT 8,'Boat','SpaceCo'
UNION ALL
SELECT 9,'Boat','NeatCarsInc'


SELECT  Vehicle_Type,Manufacturer  
FROM (
SELECT DISTINCT DENSE_RANK() OVER ( ORDER BY Vehicle_Type) AS SNO, Vehicle_Type FROM #TAB 
)VT 
INNER JOIN (
SELECT DISTINCT DENSE_RANK() OVER ( ORDER BY Manufacturer) AS SNO,  Manufacturer FROM #TAB 
)MF
ON VT.SNO= MF.SNO

+--------------+--------------+
| Vehicle_Type | Manufacturer |
+--------------+--------------+
| Boat         | NeatCarsInc  |
| Car          | SpaceCo      |
| Spaceship    | WeMakeBoats  |
+--------------+--------------+

Answer 2

SELECT DISTINCT  Id, VehicleType, Manufacturer
   FROM
   (
     SELECT 
       RANK() OVER (PARTITION BY Manufacturer ORDER BY [Id] ASC) ManufacturerRank,
       Id,
       VehicleType,
       Manufacturer
     FROM
     (
       SELECT 
         RANK() OVER (PARTITION BY VehicleType ORDER BY [Id] ASC) VehicleRank,
         Id,
         VehicleType,
         Manufacturer
       FROM
         Vehicles
     ) RankedPerVehicleType
     WHERE VehicleRank = 1
   ) RankedPerManufacturer
   WHERE ManufacturerRank = 1

Answer 3

Since you don't seem to care about id, this could do the trick:

select distinct [Vehicle Type] into #1 from table_1
select distinct [Manufacturer] into #2 from table_1
select * from #1 cross join #2 
drop table #1
drop table #2