-6
 (select top 1 percent A from B order by newid())

i want to get the random record from table B

Kermit
  • 33,827
  • 13
  • 85
  • 121
enigma
  • 82
  • 1
  • 2
  • 11
  • 2
    Which DBMS are you using? Oracle? Postgres? And is that the complete statement or part of another? –  Feb 25 '13 at 16:28
  • @a_horse_with_no_name I would guess SQL Server by the use of `NEWID()` and `TOP` – Kermit Feb 25 '13 at 16:29
  • i am using SQL server and i want to run the query with brackets . – enigma Feb 25 '13 at 16:30
  • Why? Just remove the brackets. `select top 1 percent A from B order by newid()` would work fine. – Martin Smith Feb 25 '13 at 16:30
  • There is nothing wrong with the syntax (that is without the outer parentheses). If I substitute A and B with names from a table that I have, it works just fine. I think that it's a problem with the names that you use. I assume that you don't actually have a table named B witha field named A, so you should show the actual code that you use. – Guffa Feb 25 '13 at 16:31
  • no i want to use the statment in Where condition – enigma Feb 25 '13 at 16:31
  • 3
    @jam - So show your complete statement with the problematic bit in context. To get "the random record from table B" you don't need to use it in a `WHERE` clause at all. – Martin Smith Feb 25 '13 at 16:32

1 Answers1

2

USE like this...

select * from B where A in 
 (select top 1 percent A from B order by newid())

OR 

select top 1 percent A from B order by newid()

Both will give you random rows every time....

EDIT :

Selecting Rows Randomly from a Large Table

Gopesh Sharma
  • 6,730
  • 4
  • 25
  • 35
  • But there's no point having the `in` at all probably. `select top 1 percent * from B order by newid()` may well be all that is needed. – Martin Smith Feb 25 '13 at 16:34
  • 2
    @GopeshSharma: the statement *without* the IN will be a faster. So in terms of performance this is **not** the right approach. –  Feb 25 '13 at 16:39
  • @a_horse_with_no_name : Yeah actually, without the IN will be faster, see my Edit – Gopesh Sharma Feb 25 '13 at 16:50
  • 1
    It's not actually clear what the OP is trying to do. if `A` is not unique then the two will have different semantics. If `A` is unique then the `IN` could be beneficial were the other selected columns to be relatively wide then it could reduce the amount of data being sorted. – Martin Smith Feb 25 '13 at 16:57