[Solved] Random query

Discuss the database features
Post Reply
luisEA
Posts: 5
Joined: Tue Dec 12, 2017 12:16 pm

[Solved] Random query

Post by luisEA »

Hello!

I'm new to base so i'm not sure how to make the query that i need for a client.... I have one table with the fields ID, type, name, and i need a query that get one random value of each type....
An example would be....

ID Type Name
1 A One
2 A two
3 D three
4 C four
5 B five
6 B six

Running the query would return one random value of each type... "One - three - four - five"

Any help? Also when i try to group by "XXX" openoffice crashes..... ( i'm running a mac 10.11.5 with openoffice 4.1.4)

Thanks in advance!
Last edited by luisEA on Fri Dec 15, 2017 10:11 am, edited 2 times in total.
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Random query

Post by Villeroy »

Code: Select all

SELECT * FROM "Table" ORDER BY RAND() LIMIT 1
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
luisEA
Posts: 5
Joined: Tue Dec 12, 2017 12:16 pm

Re: Random query

Post by luisEA »

Thank you for your answer Villeroy, but that query only returns one random element from the table... I would need it to return one element of each type...
In the example the table has a field called "Type" with 4 values ( A, B, C, D ), i need the query to return a random value of each type on the same query.

In mysql the query that i need is this:

Code: Select all

SELECT `COL 3` FROM (
    SELECT
    `COL 2`, `COL 3`
    FROM `TABLE 1`
    ORDER BY RAND()
) AS shuffled_items
GROUP BY `COL 2`
I have tested it on mysql with a table with 3 columns, and it returns one value of each "type"... now i need to translate that query to base.... But i continue to have the problem of using the Group By which crashes the openoffice...
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Random query

Post by Villeroy »

Install LibreOffice. OpenOffice Base seems to be broken on Mac computers. LibreOffice is not perfect but under active development. OpenOffice is a dead horse.
If your Base document is connected to a MySQL server, you can easily use the original query either as a view or in "direct SQL" mode (aka "pass-through SQL"). If you need this in "parsed SQL mode" (e.g. with forms/subforms) then simply replace the back ticks with double-quotes or create another query in parsed mode where you simply do: SELECT * FROM "direct_query"

I do not understand why you need the nested query with grouping.
IMHO, this simplified query should do the same :

Code: Select all

SELECT DISTINCT "COL 3" AS "shuffled_items" FROM "Table 1" ORDER BY RAND()
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
luisEA
Posts: 5
Joined: Tue Dec 12, 2017 12:16 pm

Re: Random query

Post by luisEA »

Thank you for your replays Villeroy.

I will install LibreOffice, and will try it to see how it works....
My base document has no sql table, that sql query that i posted before was me doing tests to find the right query for the things that i need but i really need the query to work with base.
The inner select get a random order for the rows of the table. The outer select returns only the values of the "col 3" grouped by the "col 2". This way, the query obtains a random value of the row "col 3" for each type of value of the row "col 2". I don't need all the rows, just one row of each type. How can i do that with base?

After installing LibreOffice and load the odb from openoffice ( or recreating in the worst case ), i will need to connect a button so that on click it will call this query and place the result in a textarea and also i will need to update some field when i change the value of a combo box... Any good tutorials for this? I'm new to base, and i'm not sure of how can i connect the elements....
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Random query

Post by chrisb »

luisEA,
below is the best i can come up with at this time. not particularly elegant i'm afraid, hopefully someone can suggest a better solution.

Code: Select all

select A.ID, A."Type", A."Name" from
(	--select every table row & rank it according to "Type"
	select ID, "Type", "Name",
	(select count(*) from "tRandom" where "Type" = R."Type" and ID < R.ID) "Rank"
	from "tRandom" R
) A

join
(	--select one row per "Type" by counting the number of rows per "Type" * multiplying by RAND()
	select "Type", round(rand() * (select count(*) from "tRandom" where "Type" = R."Type" and ID < R.ID), 0) "Rank"
	from "tRandom" R
	group by "Type"
) B
on B."Type" = A."Type" and B."Rank" = A."Rank"
order by "Type"
 Edit: forget to say YOU MUST REPLACE THE FOUR INSTANCES OF tRandom WITH THE NAME OF YOUR TABLE. 
 Edit: 15-Dec-2017 05:03 HAVING TAKEN THE TIME TO REVIEW MY INITIAL CODE I NOW REALISE THAT THE FIRST LINE IN THE SECOND QUERY IS POORLY CODED. PLEASE USE THE CODE BELOW. 

Code: Select all

select A.ID, A."Type", A."Name" from
(	--select every table row & rank it according to "Type"
	select ID, "Type", "Name",
	(select count(*) from "tRandom" where "Type" = R."Type" and ID < R.ID) "Rank"
	from "tRandom" R
) A
join
(	--select one row per "Type" by counting the number of rows per "Type" & multiplying by RAND()
	select "Type", floor(rand() * count(*)) "Rank"
	from "tRandom"
	group by "Type"
) B
on B."Type" = A."Type" and B."Rank" = A."Rank"
order by "Type"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
luisEA
Posts: 5
Joined: Tue Dec 12, 2017 12:16 pm

Re: Random query

Post by luisEA »

Chrisb,

I can't thank you enough, that query works perfectly!. Now i can continue with it... :bravo: :bravo:

Thank you community!
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4
Post Reply