How to select distinct value from multiple tables given a column filter?

I have this table

Products

Id	ProductName	ProductCode
1	Alexa	P0001
2	Alexa	P0002
3	Alexa 2	P0003
4	Aquarium	P0004
5	Aquarium X	P0004
6	Bathtub	P0005

CREATE PROCEDURE [dbo].[usp_GetFilter]
    @filter nvarchar(max),
    @search nvarchar(max),
    @rows int = 10
AS
BEGIN
    DECLARE @return TABLE([id] INT, [value] NVARCHAR(50));

    IF (@filter = 'ProductCode')
    BEGIN
        INSERT INTO @return([value], [id])
        SELECT DISTINCT TOP (@rows) [Code] AS ProductCode, ProductId Id
        FROM [dbo].[Products]
        WHERE [Code] LIKE CONCAT(@search, '%')
        ORDER BY [Code];
    END
    IF (@filter = 'ProductName')
    BEGIN
        INSERT INTO @return([value], [id])
        SELECT DISTINCT TOP (@rows)[Name] AS ProductName, ProductId Id
        FROM [dbo].[Products]
        WHERE [Name] LIKE CONCAT(@search, '%')
        ORDER BY [Name];
    END

    SELECT DISTINCT TOP (@rows) [id], [value] 
    FROM @return
    ORDER BY [value]
END

Scenario 1:

declare @filter nvarchar(max) = 'ProductName'
declare @search nvarchar(max) = 'A'
declare @rows int = 10    

exec [dbo].[usp_GetFilter] filter, @search, @rows

Expected Result: Returns distinct ProductName along with Id

Id	ProductName
1	Alexa
3	Alexa 2
4	Aquarium
5	Aquarium X

Actual result:

Returns all product name starting with ‘A’ distinct not working

Scenario 2:

declare @filter nvarchar(max) = 'ProductCode'
declare @search nvarchar(max) = 'P000'
declare @rows int = 10    

exec [dbo].[usp_GetFilter] filter, @search, @rows

Expected Result: Returns distinct Product Code along with Id

Id	ProductCode
1	P0001
2	P0002
3	P0003
4	P0004
6	P0005

Actual result:

Returns all product Code starting with ‘P000’ distinct not working

Answer

Check The Below Code:

Use MIN function

DECLARE
@filter nvarchar(max) = 'ProductCode',
@search nvarchar(max)='P000',
@rows int = 10

BEGIN
DECLARE @return TABLE([id] INT, [value] NVARCHAR(50));

IF (@filter = 'ProductCode')
BEGIN
    INSERT INTO @return([value], [id])
    SELECT TOP (@rows) [Code] AS ProductCode,  MIN( Id)
    FROM @Products
    WHERE [Code] LIKE CONCAT(@search, '%')
     GROUP BY [Code]
    ORDER BY [Code];
END
IF (@filter = 'ProductName')
BEGIN
    INSERT INTO @return([value], [id])
    SELECT TOP (@rows)[Name] AS ProductName, MIN( Id)
    FROM @Products
    WHERE [Name] LIKE CONCAT(@search, '%')
    GROUP BY [Name]
    ORDER BY [Name];
END

SELECT TOP (@rows) [id], [value] 
FROM @return
ORDER BY [id],[value]
END