LS.

a)
I have a table with a autoinc field as PK and a couple of other fields. I
want to show the records in a random order (so each time I open the table
the records are shown in a grid (or list)  in a different record order). How
can one achieve this?

b)
To refine this even more: if there is also a field 'typename' (string) how
to randomize in such a way that per typename a record is shown random, then
the next typename etc.  So e.g:

type 1, 3
type 2, 1
type 3, 4
type 1, 2
type 2, 7
etc.

Any ideas or directions to point to?

Regards, Harry

Harry

You use a query rather than a table in the SELECT clause you have something like  RAND (RANGE 999999) AS _Random. That creates a random sequence for you. You can then manipulate ORDER BY and GROUP BY to give the right effect.

I've added the query I'm using below. Its probably a bit more than you would need but it gives you the idea.

You might (as I am) have to select into a memory or named temporary table so you can action it further.


Roy Lambert



DROP TABLE IF EXISTS "Memory\$CallList";

SELECT
_CallID,
_fkContacts,
_fkCompanies,
_fkSites,
_Priority,
_LastResult,
RAND (RANGE 999999) AS _Random,
_Done,
_IsCandidate,
J._Secretary,

IF(M._MktDesc IS NOT NULL  AND O._Description IS NOT NULL  THEN M._MktDesc +' (' + O._Description + ')'
ELSE M._MktDesc + O._Description) AS _MarketAndType,

IF(C._Name IS NOT NULL THEN  C._Name ELSE   J._UnlistedCompany + ' ??') +
IF(C._Status IS NOT NULL THEN  ' (' + C._Status+')' ELSE '')
AS _CompanyAndStatus,

IF(SG._Description IS NOT NULL  AND SC._Description IS NOT NULL  THEN SG._Description + ': ' + SC._Description
ELSE SG._Description +  SG._Description) AS _SIC,

IF(J._MainPhone IS NOT NULL  THEN J._MainPhone
ELSE  Site._Switchboard) AS _MainPhone,

IF(T._Turnover IS NOT NULL  AND S._Staff IS NOT NULL  THEN T._Turnover +', '+S._Staff+' staff'
ELSE
IF(T._Turnover IS NOT NULL  THEN T._Turnover
ELSE
IF(S._Staff IS NOT NULL  THEN S._Staff+' staff' ELSE ''))) AS _Size,

W._Title+' '+  W._Forename+' '+W._Surname AS _FullName,

IF(W._Status IS NOT NULL  AND J._JobTitle IS NOT NULL  THEN J._JobTitle + ' (' + W._Status+')'
ELSE J._JobTitle + W._Status) As _JobAndStatus,

W._Forename,
W._Surname,
W._HomePhone,
W._Mobile,
W._HomeEMail,
W._Status,
IF(W._LatestCV IS NOT NULL THEN TRUE ELSE FALSE) AS _HasCV,
J._EMail,
J._fkJobCodes,
IF(J._Left IS NOT NULL THEN TRUE ELSE FALSE) AS _StillEmployed,
IF (C._Name IS NOT NULL THEN  C._Name ELSE   J._UnlistedCompany + ' ??') AS _CompanyName,

Site._Town,
Site._County,
Site._Country,
('Site: '+
Site._SiteName +
IF(Site._Town IS NOT NULL THEN ' - ' + Site._Town ELSE '') +
IF(Site._County IS NOT NULL THEN ', ' + Site._County ELSE '') +
IF(Site._Country IS NOT NULL THEN ', ' + Site._Country ELSE '')
)AS _Location,

C._fkMarkets,
C._fkOrgType,
Site._Switchboard,
S._Staff AS _Staffing,
T._Turnover AS _Turnover,
C._fkSICGroup,
C._fkSICCode,
C._Status AS _CompanyStatus,
C._Website,
IF(P._Name IS NOT NULL THEN 'Parent: '+ P._Name ELSE '') AS _Parent
INTO "Memory\$CallList"
FROM Calls
LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID
LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID
LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID
LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID
LEFT OUTER JOIN Sites Site ON  J._fkCompanies = Site._fkCompanies AND J._fkSites = Site._SiteID
WHERE
_fkProjects = :ProjectID
AND
(_SleepUntil IS NULL OR _SleepUntil <= CURRENT_DATE)
AND
(C._Name IS NOT NULL OR W._Surname IS NOT NULL);

CREATE INDEX IF NOT EXISTS "Priority:Contact" ON "Memory\$CallList" (_Priority,_Surname,_Forename);
CREATE INDEX IF NOT EXISTS "Priority:Company" ON "Memory\$CallList" (_Priority,_CompanyName);
CREATE INDEX IF NOT EXISTS "Random" ON "Memory\$CallList" (_Random);
CREATE INDEX IF NOT EXISTS "Contact" ON "Memory\$CallList" (_Surname,_Forename);
CREATE INDEX IF NOT EXISTS "Company" ON "Memory\$CallList" (_CompanyName);
CREATE INDEX IF NOT EXISTS "ContactCode" ON "Memory\$CallList" (_fkContacts);
CREATE INDEX IF NOT EXISTS "CompanyCode" ON "Memory\$CallList" (_fkCompanies);
CREATE INDEX IF NOT EXISTS "Done" ON "Memory\$CallList" (_Done);
CREATE INDEX IF NOT EXISTS "Status" ON "Memory\$CallList" (_Status);

SELECT
_fkContacts, _fkCompanies, _CallID, COUNT(1)AS Dedup
INTO "Memory\Dedup"
FROM "Memory\$CallList"
GROUP BY _fkContacts, _fkCompanies
HAVING Dedup > 1;

DELETE FROM "Memory\$CallList"
JOIN "Memory\Dedup" D ON _fkContacts = D._fkContacts AND _fkCompanies = D._fkCompanies
WHERE _StillEmployed;

DROP TABLE IF EXISTS "Memory\Dedup";

Roy,

This looks great, but (I should have mentioned) we use dbIsam 3.3 and there
is no RAND function available as far as I know.

Regards, Harry




"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:0A14BC9C-A438-4540-906D-3CB21B5E2651@news.elevatesoft.com...
> Harry
>
> You use a query rather than a table in the SELECT clause you have
something like RAND (RANGE 999999) AS _Random. That creates a random
sequence for you. You can then manipulate ORDER BY and GROUP BY to give the
right effect.
>
> I've added the query I'm using below. Its probably a bit more than you
would need but it gives you the idea.
>
> You might (as I am) have to select into a memory or named temporary table
so you can action it further.
>
>
> Roy Lambert
>
>
>
> DROP TABLE IF EXISTS "Memory\$CallList";
>
> SELECT
> _CallID,
> _fkContacts,
> _fkCompanies,
> _fkSites,
> _Priority,
> _LastResult,
> RAND (RANGE 999999) AS _Random,
> _Done,
> _IsCandidate,
> J._Secretary,
>
> IF(M._MktDesc IS NOT NULL AND O._Description IS NOT NULL THEN M._MktDesc
+' (' + O._Description + ')'
> ELSE M._MktDesc + O._Description) AS _MarketAndType,
>
> IF(C._Name IS NOT NULL THEN C._Name ELSE J._UnlistedCompany + ' ??') +
> IF(C._Status IS NOT NULL THEN ' (' + C._Status+')' ELSE '')
> AS _CompanyAndStatus,
>
> IF(SG._Description IS NOT NULL AND SC._Description IS NOT NULL THEN
SG._Description + ': ' + SC._Description
> ELSE SG._Description + SG._Description) AS _SIC,
>
> IF(J._MainPhone IS NOT NULL THEN J._MainPhone
> ELSE Site._Switchboard) AS _MainPhone,
>
> IF(T._Turnover IS NOT NULL AND S._Staff IS NOT NULL THEN T._Turnover +',
'+S._Staff+' staff'
> ELSE
> IF(T._Turnover IS NOT NULL THEN T._Turnover
> ELSE
> IF(S._Staff IS NOT NULL THEN S._Staff+' staff' ELSE ''))) AS _Size,
>
> W._Title+' '+ W._Forename+' '+W._Surname AS _FullName,
>
> IF(W._Status IS NOT NULL AND J._JobTitle IS NOT NULL THEN J._JobTitle + '
(' + W._Status+')'
> ELSE J._JobTitle + W._Status) As _JobAndStatus,
>
> W._Forename,
> W._Surname,
> W._HomePhone,
> W._Mobile,
> W._HomeEMail,
> W._Status,
> IF(W._LatestCV IS NOT NULL THEN TRUE ELSE FALSE) AS _HasCV,
> J._EMail,
> J._fkJobCodes,
> IF(J._Left IS NOT NULL THEN TRUE ELSE FALSE) AS _StillEmployed,
> IF (C._Name IS NOT NULL THEN C._Name ELSE J._UnlistedCompany + ' ??') AS
_CompanyName,
>
> Site._Town,
> Site._County,
> Site._Country,
> ('Site: '+
> Site._SiteName +
> IF(Site._Town IS NOT NULL THEN ' - ' + Site._Town ELSE '') +
> IF(Site._County IS NOT NULL THEN ', ' + Site._County ELSE '') +
> IF(Site._Country IS NOT NULL THEN ', ' + Site._Country ELSE '')
> )AS _Location,
>
> C._fkMarkets,
> C._fkOrgType,
> Site._Switchboard,
> S._Staff AS _Staffing,
> T._Turnover AS _Turnover,
> C._fkSICGroup,
> C._fkSICCode,
> C._Status AS _CompanyStatus,
> C._Website,
> IF(P._Name IS NOT NULL THEN 'Parent: '+ P._Name ELSE '') AS _Parent
> INTO "Memory\$CallList"
> FROM Calls
> LEFT OUTER JOIN Companies C ON Calls._fkCompanies = C._ID
> LEFT OUTER JOIN Contacts W ON Calls._fkContacts = W._ID
> LEFT OUTER JOIN Career J ON Calls._fkCareer = J._ID
> LEFT OUTER JOIN SandT S ON C._fkSandT_Staffing = S._Band
> LEFT OUTER JOIN SandT T ON C._fkSandT_Turnover = T._Band
> LEFT OUTER JOIN Markets M ON C._fkMarkets = M._ID
> LEFT OUTER JOIN OrgType O ON C._fkOrgType = O._ID
> LEFT OUTER JOIN SICGroup SG ON C._fkSICGroup = SG._ID
> LEFT OUTER JOIN SICCode SC ON C._fkSICCode = SC._ID
> LEFT OUTER JOIN Companies P ON C._fkCompanies_Parent = P._ID
> LEFT OUTER JOIN Sites Site ON J._fkCompanies = Site._fkCompanies AND
J._fkSites = Site._SiteID
> WHERE
> _fkProjects = :ProjectID
> AND
> (_SleepUntil IS NULL OR _SleepUntil <= CURRENT_DATE)
> AND
> (C._Name IS NOT NULL OR W._Surname IS NOT NULL);
>
> CREATE INDEX IF NOT EXISTS "Priority:Contact" ON "Memory\$CallList"
(_Priority,_Surname,_Forename);
> CREATE INDEX IF NOT EXISTS "Priority:Company" ON "Memory\$CallList"
(_Priority,_CompanyName);
> CREATE INDEX IF NOT EXISTS "Random" ON "Memory\$CallList" (_Random);
> CREATE INDEX IF NOT EXISTS "Contact" ON "Memory\$CallList"
(_Surname,_Forename);
> CREATE INDEX IF NOT EXISTS "Company" ON "Memory\$CallList" (_CompanyName);
> CREATE INDEX IF NOT EXISTS "ContactCode" ON "Memory\$CallList"
(_fkContacts);
> CREATE INDEX IF NOT EXISTS "CompanyCode" ON "Memory\$CallList"
(_fkCompanies);
> CREATE INDEX IF NOT EXISTS "Done" ON "Memory\$CallList" (_Done);
> CREATE INDEX IF NOT EXISTS "Status" ON "Memory\$CallList" (_Status);
>
> SELECT
> _fkContacts, _fkCompanies, _CallID, COUNT(1)AS Dedup
> INTO "Memory\Dedup"
> FROM "Memory\$CallList"
> GROUP BY _fkContacts, _fkCompanies
> HAVING Dedup > 1;
>
> DELETE FROM "Memory\$CallList"
> JOIN "Memory\Dedup" D ON _fkContacts = D._fkContacts AND _fkCompanies =
D._fkCompanies
> WHERE _StillEmployed;
>
> DROP TABLE IF EXISTS "Memory\Dedup";
>
>
>
>

Harry


In that case select into a temp table and include in the select clause something like 0 AS RANDOM then go through the result set navigationally populating the random field.....


Roy Lambert

Roy,

Thanks for the ideas. I solved it by adding a random field in a memtable
and:

 with tResultaat do begin
   First;
   Randomize;
   while not eof do begin
     Edit;
     fieldvalues['random'] := Random;
     Post;
     Next;
   end;
   SortOn('random',[]);
   First;
 end;

Regards, Harry



"Roy Lambert" <roy.lambert@skynet.co.uk> schreef in bericht
news:C7F9F822-8698-4733-B03E-8CC8E3A42551@news.elevatesoft.com...
> Harry
>
>
> In that case select into a temp table and include in the select clause
something like 0 AS RANDOM then go through the result set navigationally
populating the random field.....
>
>
> Roy Lambert
>