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!
[Solved] Random query
[Solved] Random query
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
Re: Random query
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Random query
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:
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...
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`
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4
Re: Random query
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 :
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Random query
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....
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
Re: Random query
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.
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
Re: Random query
Chrisb,
I can't thank you enough, that query works perfectly!. Now i can continue with it...
Thank you community!
I can't thank you enough, that query works perfectly!. Now i can continue with it...
Thank you community!
MAC El Capitan - 10.11.5 - OpenOffice 4.1.4