Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-05-27 : 10:41:53
|
| Jarmo writes "That subject was ugly, but it at least somehow tells you what is my SQL -problem. So now to the business:We have SQL Server 2000 -database with info about 220.000 images. Images are from different companies, we have now 10 different producers who deliver images to our picturesearch -site. HOW can I sort the search that the "searchengine" takes one record from one producer, then one from another etc. and continues to last producer and then start again from the first. So then we could have the search that every image on searchsite is from different producer. That is fair for every producer because some producers can have about 10.000 images with keyword "nature" in our database and those images appears always before another producer´s 10 images. I´ve tested the NewID() -sorting but it´s not the right solution because also then first 1000 images can be from the same producer.So in our database we have these fields: "Producer", "image", "categories". I hope you understood my problem, it´s hard to explain it but it´s much harder to code it.. At least for me :(" |
|
|
kselvia
Aged Yak Warrior
526 Posts |
Posted - 2004-05-27 : 23:02:07
|
| If you want to ensure each producer is represented on the first page, what happens when you get to the 10th page and only the producers with 10,000 images remain to be seen? Should the search position be remembered between web clients so user 2 begins displaying where user 1 left off or would the image list start at the beginning for each user? |
 |
|
|
Jarmo
Starting Member
1 Post |
Posted - 2004-05-31 : 11:43:00
|
| Thanks, kselvia!Of course when most images are from one producer (some producers have, for example, much more nature photos than others), that producer get more "showtime" than others. My idea was that search should sort the result so that first it takes "one-by-one" from different producers (if there are more than 1 with given keyword) and try to show the result first like that method. After there is only one producer left, then all images from that producer can appear.Image list can start at the beginning for each user.I´ve tried to think the right method to handle this query but with no luck. I know that it should check the "Producer" -field somehow but how to build the right sorting -system without having the search-engine getting slower.. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-05-31 : 11:52:52
|
| Put them into a temp table with producerID and newIDthenupdate #tset seq = (select count(*) from #t t2 where t1.producerID = t2.producerID and t2.guid >= t1.guid)from #t t1then order by seq for the output.You can probably also do this with derived tables in a single query if you wish.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Sorting results like "every second from different field value"