SELECT DISTINCT not working with INNER JOIN and ORDER BY newid()-CodePudding

I am trying to query a database of Wines and Wineries, to get 4 random wines from different wineries. Unfortunately, I still see some duplicate wineries in my results and adding "DISTINCT" to my query doesn't work with the newid() function. I have also tried "GROUP BY" and Sub queries with no solution. Any suggestions?

SELECT TOP(4) w.ID_Winery, w.appellation, w.delete_wine, w.Description_brief, w.wine_vintage, a.ID_Winery, a.weblogin, a.Winery, a.WebSiteUrl 
FROM Wines w
INNER JOIN Wineries a
    ON w.ID_Winery = a.ID_Winery
WHERE w.appellation = 'Lorem Ipsum'
    AND a.weblogin !=""
    AND w.delete_wine !=1
    AND a.Winery = 1
    AND a.WebSiteUrl !=""
    AND a.okWinery = "yes"
    AND w.Description_brief
        LIKE '%Lorem Ipsum%'
ORDER BY newid()

CodePudding user response：

This should do it:

SELECT TOP 4 *
FROM (
    SELECT w.ID_Winery, w.appellation, w.delete_wine, w.Description_brief, 
        w.wine_vintage, a.ID_Winery, a.weblogin, a.Winery, a.WebSiteUrl,
        row_number() over (partition by w.ID_Winery order by newid()) rn
    FROM Wines w
    INNER JOIN Wineries a
        ON w.ID_Winery = a.ID_Winery
    WHERE w.appellation = 'Lorem Ipsum'
        AND a.weblogin !=""
        AND w.delete_wine !=1
        AND a.Winery = 1
        AND a.WebSiteUrl !=""
        AND a.okWinery = "yes"
        AND w.Description_brief
            LIKE '%Lorem Ipsum%'
) t
WHERE rn=1
ORDER BY newid()

FWIW, I'm not a fan of ordering by newid.