By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,190 Members | 1,412 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,190 IT Pros & Developers. It's quick & easy.

Preserve order "WHERE IN(...)"

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Frederik wrote:
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


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.
Nov 13 '05 #2

P: n/a
On 29 Sep 2004 09:57:10 -0700, ae************@hotmail.com (Frederik)
wrote:
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


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
Nov 13 '05 #3

P: n/a
gj
Frederik wrote:
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


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
Nov 13 '05 #4

P: n/a
> "John Vinson" wrote...
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]);


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
Nov 13 '05 #5

P: n/a
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.
Nov 13 '05 #6

P: n/a
> "Joep" <St***@DeStoep.nl> wrote...
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.


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
Nov 13 '05 #7

P: n/a
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.
Nov 13 '05 #8

P: n/a
On 30 Sep 2004 06:45:46 -0700, ae************@hotmail.com (Frederik)
wrote:
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


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
Nov 13 '05 #9

P: n/a
> 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


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
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.