CODEWITHSUNDEEP
asp.netms-accessunion
user3250818
user3250818

Reputation: 247

Order by doesnt work with union, microsoft access database, asp.net

I want to fetch data by percent from database based On rate field, also I want to show data randomly so I tried this

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

It shows correct data but not randomly, any suggestion?

also I provided a sample for test purpose you can download from link below http://www.4shared.com/zip/qMSYRglGce/testorderby.html

Upvotes: 1

Views: 589

Answers (1)

Steve
Steve

Reputation: 216293

In an UNION query the ORDER BY should be applied to the whole set returned by the UNION
So you build a set of records from the three subselects then apply the order to the resulting set.

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

Upvotes: 3

Related Questions