# select percent of data based on rate field

• ### Question

• User-1693623980 posted

Hi,

consider we have a table for storing User's advertisements and we have a field for storing rate of each ad

this rate can be between 0 and 7

now I want to give chances to show ads in first page based on the number of stars
for example like below
7 star 100%
6 star 84%
5 star 72%
4 star 60%
3 star 48%
2 star 36%
1 star 24%
0 star 12%

I know we can fech for example 50% of all records using below code
SELECT TOP 50 PERCENT * FROM Customers;
but I dont know how can I fetch data based on percent when we have more limitation like what I said before.

thanks

Saturday, August 16, 2014 9:16 AM

### All replies

• User-1025250470 posted

I dont think Top 50% is the right way. Why you cannot pull record based on where Condition. Simple query.

Eg: Select Top 1 Field from YourTable where Rating=7

Thank You

Saturday, August 16, 2014 11:22 AM
• User-1693623980 posted

This one works except those ORDER BY Sections

```(SELECT TOP 100 PERCENT * FROM tbl_adv WHERE Stars = 7 ORDER BY Rnd(-(1000*adID)*Time()))
UNION (SELECT TOP 84 PERCENT * FROM tbl_adv WHERE Stars = 6 ORDER BY Rnd(-(1000*adID)*Time()))
UNION (SELECT TOP 72 PERCENT * FROM tbl_adv WHERE Stars = 5 ORDER BY Rnd(-(1000*adID)*Time()))```

I expected to above query fetch data randomly, but each time the resultset orders are the same!

what's the problem?

also I heard select top percent imposes a lot of pressure to the server. is it true?

thanks

Saturday, August 16, 2014 3:36 PM
• User1918509225 posted

Hi Bahare Feizi,

The only prerequisite is that you have an AutoNumber adID column.

It should also be noted that although this generates  a random set of records, they are not truely random in a mathematical sense, but should be sufficiently random for most uses.

Here is a link which has similar issue with yours,which may give your  a right direction:

Last , i suggest that you can post your question to the link below ,which is more related with your sql query question:

http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition

Best Regards,

Kevin Shen.

Monday, August 18, 2014 10:32 PM
• User-1199946673 posted

Here is a link which has similar issue with yours,which may give your  a right direction:

No it doesn't. When working in the Access Windows environment, the Rnd function will work. However, when you're working with a MDB file in a web environment, it doesn't work as expected

http://forums.asp.net/post/1901866.aspx

Last , i suggest that you can post your question to the link below ,which is more related with your sql query question:

http://forums.asp.net/1226.aspx/1?SQL+Server+SQL+Server+Express+and+SQL+Compact+Edition

Why? Is's about Access, so this is the right forum....

Tuesday, August 19, 2014 8:29 AM
• User-1199946673 posted

```ORDER BY Rnd(-(1000*adID)*Time()))
```

Try using a much higher negative number?

Rnd(-100000000*[ID]*Time())

Tuesday, August 19, 2014 8:37 AM
• User-1693623980 posted

thanks hans and sorry for late,

I tried your suggestion but it didn't work.

this one works properly

`string sql = "      (SELECT TOP 100 PERCENT * FROM tbl_adv WHERE Stars = 7 ORDER BY Rnd(-(1000*adID)*Time())) ";`

but as soon as I add union prats like below it doesn't work anymore

`string sql = "      (SELECT TOP 100 PERCENT * FROM tbl_adv WHERE Stars = 7 ORDER BY Rnd(-(1000*adID)*Time())) UNION (SELECT TOP 84 PERCENT * FROM tbl_adv WHERE Stars = 6 ORDER BY Rnd(-(1000*adID)*Time())) UNION (SELECT TOP 72 PERCENT * FROM tbl_adv WHERE Stars = 5 ORDER BY Rnd(-(1000*adID)*Time()))";`

http://www.4shared.com/zip/qMSYRglGce/testorderby.html

Saturday, August 23, 2014 2:53 AM
• User-1693623980 posted

hi,

the problem solved,

In an UNION query the ORDER BY should be applied to the whole set returned by the UNION as Steve said at the below link

http://stackoverflow.com/questions/25459416/order-by-doesnt-work-with-union-microsoft-access-database-asp-net

thanks a lot

• Marked as answer by Thursday, October 7, 2021 12:00 AM
Saturday, August 23, 2014 5:10 AM