[sqlite] Problem with distinct select query

[R Smith]

On 2016/01/29 4:51 PM, Gary Baranzini wrote:
> Hi,
>
> I have a query where I select 20 random distinct rows.
>
> SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
> majorfcategory.item_name
> FROM formulas
> JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
> ORDER BY RANDOM() LIMIT 20
>
> Each row contains a major category id, "majorcategory"
> I want to improve on this query so that the select will NOT return 
> duplicate major categories
>
> I thought I could do:
>
> SELECT DISTINCT formulas.pinyin, formulas.majorcategory, 
> majorfcategory.item_name
> FROM formulas
> JOIN majorfcategory ON majorfcategory.id=formulas.majorcategory
> WHERE formulas.majorcategory in (SELECT DISTINCT 
> formulas.majorcategory FROM formulas) ORDER BY RANDOM() LIMIT 20
>
> But that doesn't work.
>
> Any suggestions will be greatly appreciated.

A few ways come to mind, here's an easy one that works for me:


WITH RNDCAT(cat) AS (       -- CTE to list 20 random categories
     SELECT DISTINCT formulas.majorcategory FROM formulas ORDER BY 
RANDOM() LIMIT 20
), RNDFRM (cat, pin) AS (    -- CTE to randomize the formula list
     SELECT majorcategory, pinyin FROM formulas ORDER BY RANDOM()
), RNDVAL(cat, pin) AS (     -- CTE to link one random-list formula to 
each category
     SELECT RNDCAT.cat, (SELECT pin FROM RNDFRM WHERE 
RNDFRM.cat=RNDCAT.cat LIMIT 1) FROM RNDCAT
)      -- Final select to show values and names
SELECT RNDVAL.pin AS Pinyin, RNDVAL.cat AS Category_ID, MC.item_name AS 
Category
   FROM RNDVAL
   LEFT JOIN majorfcategory AS MC ON MC.id=RNDVAL.cat
ORDER BY RNDVAL.cat
;

Cheers,
Ryan