Preserve order "WHERE IN(...)"

  • Thread starter Thread starter Frederik
  • Start date Start date
F

Frederik

Hi all,

I'm building a C# application that uses a M$ Acces database. In one of
the queries I use something like the following:

SELECT id FROM mytable WHERE id IN (20, 12, 21, 14)

The result is fine, except that the order of the id's is not
preserved. It seems to be ordered ascending. How can I keep the order
I used as input?

[Background: after I collected the records I want, they need to be put
in random order. So the numbers between the braces are the result of a
Randomize method.]

Thanks a lot for your time,
Frederik
 
Frederik said:
Hi all,

I'm building a C# application that uses a M$ Acces database. In one of
the queries I use something like the following:

SELECT id FROM mytable WHERE id IN (20, 12, 21, 14)

The result is fine, except that the order of the id's is not
preserved. It seems to be ordered ascending. How can I keep the order
I used as input?

[Background: after I collected the records I want, they need to be put
in random order. So the numbers between the braces are the result of a
Randomize method.]

Thanks a lot for your time,
Frederik
Click to expand...

Maybe create a temp table with 2 fields; ID and Rank. 20=1,12=2,21=3,
etc. Then link your sqlstring to the temp table and sort on the Rank.
 
The WHERE clause does not change the order in which the
table is processed. In order to out the results in the
order that you want, you could use an ORDER BY clause along
the lines of
ORDER BY Switch([id]=20,1,[id]=12,2,[id]=21,3,[id]=14,4)

Hope This Helps
Gerald Stanley MCSD
 
Thanks! The result is fine now!
Frederik

Gerald Stanley said:
The WHERE clause does not change the order in which the
table is processed. In order to out the results in the
order that you want, you could use an ORDER BY clause along
the lines of
ORDER BY Switch([id]=20,1,[id]=12,2,[id]=21,3,[id]=14,4)

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Hi all,

I'm building a C# application that uses a M$ Acces database. In one of
the queries I use something like the following:

SELECT id FROM mytable WHERE id IN (20, 12, 21, 14)

The result is fine, except that the order of the id's is not
preserved. It seems to be ordered ascending. How can I keep the order
I used as input?

[Background: after I collected the records I want, they need to be put
in random order. So the numbers between the braces are the result of a
Randomize method.]

Thanks a lot for your time,
Frederik
.
Click to expand...
Click to expand...
 
Hi all,

I'm building a C# application that uses a M$ Acces database. In one of
the queries I use something like the following:

SELECT id FROM mytable WHERE id IN (20, 12, 21, 14)

The result is fine, except that the order of the id's is not
preserved. It seems to be ordered ascending. How can I keep the order
I used as input?

[Background: after I collected the records I want, they need to be put
in random order. So the numbers between the braces are the result of a
Randomize method.]

Thanks a lot for your time,
Frederik
Click to expand...

An Access Table HAS NO ORDER - and a Query has no controlled order
either, unless you include an Order By clause. If you truly want a
random order (which, in this case, will be independent of the order of
arguments in the IN clause) use

SELECT id FROM mytable WHERE id IN(12, 14, 20, 21)
ORDER BY Rnd([ID]);


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Frederik said:
Hi all,

I'm building a C# application that uses a M$ Acces database. In one of
the queries I use something like the following:

SELECT id FROM mytable WHERE id IN (20, 12, 21, 14)

The result is fine, except that the order of the id's is not
preserved. It seems to be ordered ascending. How can I keep the order
I used as input?

[Background: after I collected the records I want, they need to be put
in random order. So the numbers between the braces are the result of a
Randomize method.]

Thanks a lot for your time,
Frederik
Click to expand...

Use a Union query. Not sure what this will do to speed.

SELECT id FROM mytable WHERE id =20
union SELECT id FROM mytable WHERE id =12
union SELECT id FROM mytable WHERE id =21
union SELECT id FROM mytable WHERE id =14
 
An Access Table HAS NO ORDER - and a Query has no controlled order
either, unless you include an Order By clause. If you truly want a
random order (which, in this case, will be independent of the order of
arguments in the IN clause) use
SELECT id FROM mytable WHERE id IN(12, 14, 20, 21)
ORDER BY Rnd([ID]);
Click to expand...

Hi John,

Your query works fast and accurate when I use it in MS Access (changed
it a little to my own needs).
The weird thing is, when I use the same query to fill a DataGrid (C#
WinForm) I get the same result every time.

For example: "SELECT TOP 8 id, latijn, nederlands FROM lexicon WHERE
datum>=1/1/2004 ORDER BY Rnd([id]);" returns a different result in
Access almost each time it is run. Filling a DataGrid on a Windows
Form results in the same order every time, although using the same
query.

I don't understand :o


************************
** Start Code Snippet **
************************

sqlSelect = "SELECT TOP 8 id, latijn, nederlands FROM lexicon WHERE
datum >= 1/1/2004 ORDER BY Rnd([id])";
OleDbCommand cmd = new OleDbCommand(sqlSelect, conn);

OleDbDataAdapter da2 = new OleDbDataAdapter(cmd.CommandText);
da2.SelectCommand = cmd;
DataTable dt = new DataTable("opvraging");

// try filling the DataTable
try {da2.Fill(dt);}
catch (OleDbException olex) {frm.ShowError(olex.Message); return; }

// adapt columns
dt.Columns[0].ColumnMapping = MappingType.Hidden; // do not show id
column
dt.Columns[1].ReadOnly = true;
dt.Columns[2].ColumnMapping = MappingType.Hidden; // do not show
translation
DataColumn vertaalKolom = new DataColumn("vertaling", typeof(String));
vertaalKolom.DefaultValue = "";
dt.Columns.Add(vertaalKolom);

// do not allow new rows or deleting rows
DataView dv = new DataView(dt);
dv.AllowDelete = false;
dv.AllowNew = false;

// fill datagrid
dg.DataSource = dv;

**********************
** End Code Snippet **
**********************

Regards,
Frederik
 
Rnd functions in general show this behaviour, ie rnd is not really random
although the distribution is. Sometimes you can 'seed' the function so that
it produces different sequences of numbers (but for the same seed the
numbers are once again the same).

If you use something time related for the seed you might get something
better.
 
Joep said:
Rnd functions in general show this behaviour, ie rnd
is not really random although the distribution is.
Sometimes you can 'seed' the function so that it
produces different sequences of numbers (but for
the same seed the numbers are once again the same).
If you use something time related for the seed you
might get something better.
Click to expand...

Do you have any example that shows me how to do that (time related
seed)? I've been trying for about 3 hours yesterday, without any
result. If I can't find it, I'm going to do the randomizing, like
before, with the help of an ArrayList and the System.Random class in
C# itself.

Regards,
Frederik
 
Did you search for 'seed' and 'random'? If you can seed your random function
then check if you can relate a numeric version of a date/time to that and
use that. That should get you what you are looking for.

Date/time does have a numeric representation so it all comes down to finding
out whether you can seed your random function. I assume you can since that
is the usual case.

Combine the two and you have your solution.

I do not have an example, I am sorry.
 
For example: "SELECT TOP 8 id, latijn, nederlands FROM lexicon WHERE
datum>=1/1/2004 ORDER BY Rnd([id]);" returns a different result in
Access almost each time it is run. Filling a DataGrid on a Windows
Form results in the same order every time, although using the same
query.

I don't understand :o
Click to expand...

I suggest you repost this - I have never used DataGrids or Windows
Forms seriously. I know it works in Access but don't know what you
would need to tweak to get it to work!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John Vinson wrote...
I suggest you repost this - I have never used DataGrids or Windows
Forms seriously. I know it works in Access but don't know what you
would need to tweak to get it to work!
John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

It seems to have something to do with caching. MS Access does not cach
the random order and C# (at least the OleDb provider) does. But I
found a solution. After trying and trying I found a seed that the Rnd
function could work with (Rnd(-1*[id]*[@varRnd])):

************
string sqlSelect = "SELECT";
if (limit != 0) sqlSelect += " TOP " + limit;
sqlSelect += " id, " + taal + " FROM lexicon WHERE
(datum >= [@datum])";
sqlSelect += " ORDER BY Rnd(-1*[id]*[@varRnd])";

OleDbCommand cmd = new OleDbCommand(sqlSelect, conn);
OleDbParameter par = new OleDbParameter("@datum",
OleDbType.Date);
par.Value = datum;
cmd.Parameters.Add(par);
Random generator = new Random(
System.DateTime.Now.Millisecond);
OleDbParameter parRnd = new OleDbParameter("@varRnd",
OleDbType.Integer);
parRnd.Value = generator.Next();
cmd.Parameters.Add(parRnd);
************

Thanks to everyone who helped me solve this problem,
Frederik
 
Back
Top