Building a multi table random row get with some rules

[POSAPAY]

Hi all,
I've never done random row selections before, but the need has come.

Basic scenario:
MS SQL Server 2000
table1 = users
table2 = postings
table1.ID = table2.USER_ID is a decimal.
table2.DATE_POSTED is a date format

Result needed is a table of the postings but:
- only one record from each user to show in every 20 records
- only show records within last 90 days of post date to now.

Once the result has come back, I'd like to use it in ASP and paginate through it.

Currently the database has like 5000 records, probably an approximately 1000 records would be an appropriate data returned.

The random factor is to display the returned rows in random order, not by post date.

While this normally would be against useful logic, there is a reason for this madness, and that is to ensure displayed records are random, and not one user floods the returned set in one screen. Each post is an advertisement, unrelated to other posts.

Any and all help is much appreciated! Thanks!
-Peter

 

[TheBugSlayer]

Are there exactly 20 different user types? Seems impossible but it would help to know. If not then I believe it would help to determine the number of distinct User_Ids first. I am thinking that you need to use the DENSE function and partition by User_Id; you select all matching records first in a temporary table, then pass them to another table one different User_Id at a time. Then your ASP logic would only need to page through the disctinct number of User_Ids at a time. More details would help.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[POSAPAY]

Hi, Thanks for your quick response!
There are thousands of users. There are two types of users, and for the returned data, I only need users that have a type = 1 (it's a boolean)

so for lets say 4000 total users(made up), if every even number user was type=1, we'd have 2000 users.
But only let's say a 1000 has posted, and out of that only 400 have posted multiple postings. For simplicity, let's say the 400 with multiple postings, each has 10 postings, and everyone else has only 1.
So 400 x 10 + 600 = 4600 postings would be the total number of postings in the database.
But to show 20 postings per page, and each page only having one posting from each user, would mean that we need 20 (random) users that have at least one posting, then grab all postings for them(?)


Since this search could also include a "searchkeyword" that gets matched with couple of fields in the posting, the search could happen many many times.
(To-date the site's busiest day we've had 1000 unique visitors)It is unlikely, but worst possible case would be let's say 2000 search commands fired-off at the system simultaneously.

If we can come up with a fine-tuned quick search result for that, it would be really awesome!

 

[TheBugSlayer]

Some thing like this may help...

use adventureworks

select dense_rank() over (partition by SalesOrderID order by SalesOrderDetailID) as dr, * 
into #vend
from Sales.SalesOrderDetail
where OrderQty > 10

declare @maxrank int
select @maxrank = max(dr) from #vend
declare @cnt int
select @cnt = 1

while @cnt < @maxrank
begin
   	select * from #vend where dr = @cnt

    select @cnt = @cnt + 1
end
select * from #vend2

drop table #vend
drop table #vend2

or a variation like

use adventureworks

select dense_rank() over (partition by SalesOrderID order by SalesOrderDetailID) as dr, * 
into #vend
from Sales.SalesOrderDetail
where OrderQty > 10

declare @maxrank int
select @maxrank = max(dr) from #vend
declare @cnt int
select @cnt = 2

select * 
into #vend2
from #vend where dr = 1
while @cnt < @maxrank
begin
    insert #vend2
	select * from #vend where dr = @cnt

    select @cnt = @cnt + 1
end
select * from #vend2

drop table #vend
drop table #vend2

, which fails due to an identity column violation...Not much time to look deeper...Maybe Monday.

Good luck.


Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

You may want to take a look at this blog that I wrote a while ago. I think it does a decent job of showing you how to accommodate random rows.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-set-based-random-numbers

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[POSAPAY]

George,
Thanks for that blog / test results on random using NewID().
I've read up on the NewID() in the past week, and now I'm trying to use it, but there are extra complications.

Currently when I do my select without any random factors, I get on the first page 20 postings form the same user, and since these are job postings, often they post the same job posting title with simply different city locations, or categories. In order to have a better value in initial search return, I'd like to only show one posting from each user, and randomly pick one of their postings to display.

Yet, I'd like to paginate through all the data when someone really wants to dig in. Any idea how Google would do this?

Or perhaps I should use grouping and show the first posting only?

SELECT * FROM table2 WHERE table1.ID = table2.user_id
Then group by table1.ID somehow?

Curious if you've had to deal with something similar in the past. Thanks for any and all tips and ideas!
-peter

 

[markros]

In SQL Server 2005 and up pagination is very easy with ROW_NUMBER() function.

Also, check this query MSDN thread showing how easy is to get random records based on partition in SQL Server 2005.

Unfortunately, in SQL 2000 this problem becomes more complex...

PluralSight Learning Library

 

[POSAPAY]

Thanks for the info. Unfortunately I'm stuck on SQL2000 for this project.

I'm not too good yet with group-ing, would it be something like:

SELECT * FROM table2 WHERE table1.ID = table2.user_id
Then group by table1.ID somehow?

Thnks, - Peter

 

[POSAPAY]

Anybody can help me with the grouping to get one posting from each user only?
I'll experiment with the randomizer from there on. Just need that one little help. Thanks!