sql query returning multiple result using WHERE

Question

There is a table name Top_Up. Its current snapshot is: Top_Up Table

When I run query SELECT * FROM Top_Up WHERE Top_up_ID = (round(random() * 9 ) + 1); on it, I am getting random result. Sometimes it is returning with two tuples, sometimes no tuples and sometime one tuple.

To debug I run the query Select (round(random() * 9 ) + 1); and it is always returning only one tuple in the result.

Why I am getting this vague and random result?

Can you post the specific rows that you get (or at least the ID values) when you get more than one row? Also, why not just do a select * from Top_Up where Top_up_ID<=10 order by random() limit 1;? — user554546
– user554546, Commented Nov 7, 2012 at 22:04

jTC · Accepted Answer · 2012-11-07 22:01:25Z

3

Round is being calculated per row. Try this:

SELECT TOP 1 * FROM Top_Up ORDER BY (round(random() * 9 ) + 1);

If you run your test against your table you should see a much different result:

 Select (round(random() * 9 ) + 1) FROM Top_Up `

If you just want a random record I would go with:

SELECT TOP 1 * FROM Top_Up ORDER BY NEWID()

answered Nov 7, 2012 at 22:01

jTC

1,3509 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jTC Over a year ago

The last query will scale better, where your original query would only select one from the first 10. Not sure if that was intentional or not though, which is why I offered both of them.

Collectives™ on Stack Overflow

sql query returning multiple result using WHERE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related