Select Rand () between From GroupBy and with OrderBy

Question

Select Rand () between From GroupBy and with OrderBy

Navigation

#1 by (1 votes)

0

I have a table with the following fields:

id
code
category
web_sales
brand

I want to show only random code in Laravel and group it by category but that the code is generated randomly.

The problem is this: how do I group the category and bring a random code grouping the category?

I have the query as follows:

SELECT codigo,categoria
FROM 'vcomp_productos_web1'
WHERE venta_web = "SI" AND marca = "VERA"
GROUP BY categoria
ORDER BY rand()

But it shows me the following table:

How do I get the code generated randomly? I have consulted and it is done with Rand(codigo) the first code is always chosen and I need any code, but I can not find the solution.

mysql sql laravel

asked by Devin Stiven Zapata Areiza 19.12.2017 в 02:42

source

1 answer

Difference between InnerHTML and Nodes? [closed] Query sql server to delete an ID and a COLUMN

score 1 · Accepted Answer

This should be done in the following way:

the query you need is this:

SELECT * FROM (
   SELECT codigo,categoria FROM 'vcomp_productos_web1'
   WHERE venta_web = "SI" AND marca = "VERA"
   ORDER BY rand()
) as sub GROUP BY categoria

To achieve this with laravel, you could try this:

$sub = DB::table('vcomp_productos_web1')
       ->select("codigo, categoria")
       ->where("venta_web","SI")
       ->where("marca", "VERA")
       ->inRandomOrder();

$result = DB::table( DB::raw("({$sub->toSql()}) as sub") )
    ->mergeBindings($sub) //$sub->getQuery() si $sub es Eloquent
    ->groupBy("categoria")
    ->get();

It should be tried, but it should work.