Menu
It's possible get a random value of the group by?
----------------
nID | val
---------------
A | XXX
A | YYY
B | L
B | M
B | N
B | P
----------------
With this SQL:
SELECT nID, VAL FROM T1 GROUP BY nID
My result always is:
nID val
--------
A XXX
B L
But i want a diferent result of evey nID. Like:
nID val
--------
A YYY
B N
or
nID val
--------
A XXX
B P
It's possible?
http://sqlfiddle.com/#!2/357b8/3
484
SQL – count() with Group By clause The count() function is an aggregate function use to find the count of the rows that satisfy the fixed conditions. The count() function with the GROUP BY clause is used to count the data which were grouped on a particular attribute of the table.
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
Order By and Group By Clause in SQL Group By in SQL is used to arrange similar data into groups and Order By in SQL is used to sort the data in ascending or descending order.
GROUP BY clause is used with the SELECT statement. In the query, GROUP BY clause is placed after the WHERE clause. In the query, GROUP BY clause is placed before ORDER BY clause if used any.
Use a sub-query.
SELECT r.nID,
(SELECT r1.val FROM T1 r1 WHERE r.nID=r1.nID ORDER BY rand() LIMIT 1) AS 'val' FROM T1 r
GROUP BY r.nID
http://sqlfiddle.com/#!2/357b8/18
119
You can use order by rand() then group by them.
Like
SELECT nID, VAL FROM (
SELECT nID, VAL
FROM T1
ORDER BY RAND()
)AS subquery
GROUP BY nID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
