Subscribe to this thread
Home - General / All posts - Making a random selection from a table in M9
danb


1,847 post(s)
#13-Oct-21 04:26

This might be a simple question, but I can't figure out how to do it in M9. I have a table with say 100 records and I want to select a number of records (say 20) randomly from this table.

In M8 I seem to remember that it was something along the lines of:

--SQL8

SELECT TOP 20 * FROM [TABLE] ORDER BY RND();

Does anyone have any pointers as to how to achieve the same in M9?

Thanks in advance.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

danb


1,847 post(s)
#13-Oct-21 04:41

This appears to work but I would be interested to know if there is a better way of doing this:

--SQL9

SELECT DISTINCT [D1].[SAMPLE_ID]

FROM [TABLE] AS [D1]

INNER JOIN

(SELECT * FROM CALL ValueSequenceRandomInt(100, 100, 0)) AS [RND]

ON [D1].[mfd_id] = [RND].[VALUE]

FETCH 20;

100 is the number records so the join randomizes the table order, the FETCH then skims off 20 records.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb

9,852 post(s)
#13-Oct-21 10:17

Dan,

This is more like your example from 8.

--SQL9

SELECT * FROM [Table]

ORDER BY

  ( -- parentheses to cast a table having one row and one field as a scalar

  TABLE CALL ValueSequenceRandom(1, [mfd_id])

  )

FETCH 20;

However, you will see that each run will select the same random sample, because a matching seed (mfd_id) is used for each call to ValueSequenceRandom.

That's both good and bad. [And it will be the same for your use of ValueSequenceRandomInt.]

To get a different sample on each run, as in 8, you'd need to add a script function to return e.g. a time-dependent seed value--or as a quick hack, an adjustable constant as a factor.

[You will know this but I will point it out. Your example does not guarantee 20 samples, in case the random sequence has duplicates. This example will always return 20 regardless.]

danb


1,847 post(s)
#14-Oct-21 03:21

Thanks Tim, that is much more elegant than my bloated attempt.

I did actually hit not returning the required number of samples as my record [mfd_id]'s were a subset of another table. But have sorted that now.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

tjhb

9,852 post(s)
#14-Oct-21 20:26

It was not bloated, it was great.

My "straighter" example was more bloated, because...

Maybe we should request an inline function to produce a single random value (it would great if this could be time-dependent), rather than having to request a table of one random value and then cast it.

My suggestion was correct, yes, but stupid.

tjhb

9,852 post(s)
#13-Oct-21 10:33

[Ignore my last point. It is technically correct but ridiculously unlikely to arise in practice.]

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.