Help with a SQL Statement

[Sparky Arbuckle]

The SQL I am using is supposed to grab only 6 titles from the database
and randomly display them on the default page. When I add more titles
to the database the initial 6 turns into 7,8,9 - 14 titles. Any
pointers. The SQL I am using is below:

Dim strSQL as string = "SELECT TOP 6 ASIN, strTitle, strArtist,
strReview, strImageDir " & _
"FROM tblDescription " & _
"ORDER BY SIN(tblDescription.decPrice * " &
rnd() & ");"

Dim objDataReader as OLEDBDataReader
objConn.Open()

Dim objCommand as new OLEDBCommand(strSQL,objConn)
dlMusic.DataSource =
objCommand.ExecuteReader(CommandBehavior.CloseConnection)
dlMusic.DataBind()
dlMusic.RepeatColumns = 2

 

[gaidar]

Hi, Sparky,

Maybe these articles are kind of help:
http://www.sqlteam.com/item.asp?ItemID=896
http://dotnetjunkies.com/WebLog/mames/archive/2004/05/11/13334.aspx

Gaidar

 

[Brian Lowe]

The SQL TOP keyword instructs the db engine to bring the top n records from
the table or result set but SQL doesn't know how to distinguish between
records with equal position in the sort order.

If your data is such that there are more than 1 records in position 6 in the
ordering it will fetch them all.

if your results are
a
b
c
d
e
f
g
h

then SELECT TOP 6 will bring back a, b, c, d, e, f

but if your result set is
a
b
c
d
e
f
f
f
f
g

then SELECT TOP 6 will bring back a, b, c, d, e, f, f, f, f

You can usually get round this by including a unique column in your sort
clause.

Brian
----@

then