[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Problem with distinct select query
From:       Gary Baranzini <conanjb () gmail ! com>
Date:       2016-01-31 17:00:31
Message-ID: 56AE3DAF.8040201 () gmx ! com
[Download RAW message or body]

Thanks for the suggestion, it works great.

> R Smith <mailto:rsmith@rsweb.co.za>
> January 30, 2016 at 6:13 AM
>
>
>
>
> 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
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> Gary Baranzini <mailto:conanjb@gmail.com>
> January 29, 2016 at 6:51 AM
> 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.
>
> jb

_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]