locked
select percent of data based on rate field RRS feed

  • 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

Answers

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:

    http://bytes.com/topic/access/answers/202891-sort-records-into-random-order

    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:

    http://bytes.com/topic/access/answers/202891-sort-records-into-random-order

    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()))";

    also I provide a sample you can download form link below

    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 Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, August 23, 2014 5:10 AM