ORDER BY NEWID() : VistaDB Support Center

How can we help you today?

Start a new topic

ORDER BY NEWID()

thomas verhees

started a topic over 5 years ago

Hello,

So I would like to get a random record from my VistaDB.

I have the following query:

SELECT TOP 1 * FROM Table1

ORDER BY NEWID();

This works in sql server but not in VistaDB, is this not supported??

Greetings,

Thomas

2 Comments

Rob Parker

said over 5 years ago

VistaDB does not currently support expressions in the ORDER BY clause.

You can work around this by including the expression as a result column and referencing it by position number or by a unique alias:

SELECT TOP 1 *, NewId() as "Order" FROM Table1

ORDER BY "Order";

Note that if the table gets large this will have significant overhead in allocating Guid values and sorting by them over the number of rows of the table. For larger tables there may be a more efficient way to pick a random row--although it gets more complex.

thomas verhees

said over 5 years ago

@RobParker ahh oke, thanks for the answer. I will try it in a different way.