Is there a way to seed the sort order of a query??

[stephenmbell]

I have a relationship in my database setting up "buddy items"

so if you select viewing item 100 (it may have buddies of items 234(rating 1), 512(rating 2), 311(rating 3)) - each item -> buddy relationship has an associated rating field. I want to display the item selected at the top, then order by rating ascending

i want it to show item 100, then sort the other items ascending by rating.

OR consider the following
item 421 has buddy items of 101(1), 232(2), and 515(3)

I would like to see 421 show up first, then order 101, 232, 515

Is this possible?

I am thinking about simply added each item to the relationship table and giving it a rating of 0 - which should work - but.. if we had a million items that would be adding a million extra rows to a table that may not necessarily need to be in there

I hope this makes sense

thanks in advance..

sb

 

[vbahelp07]

sort by item then by rating??

if not that not sure

"i want it to show item 100, then sort the other items ascending by rating"

sort by item then by rating should put it by numerical order BUT it seems there's specific Item you want to show first regardless of the numeric order then by rating order?

 

[r937]

ORDER BY IIF(Item IN ([red]100[/red],[blue]421[/blue]),0,937),BuddyItem

if this doesn't do it, it's because i'm guessing at your intentions and also at your column names

r937.com | rudy.ca

 

[stephenmbell]

If you think of it like this:

Items Table
------------
item
100
101
102
103
....
500
501
502
....

BuddyItems
-----------
id item buddy item rating
1 100 235 1
2 100 115 2
3 201 100 1
4 201 567 2
5 201 115 3
6 201 212 4


so basically....
on my form (continuous form) if a user selects item 201

I want the information (description, price, etc...) to display in the following order

201
-information....

100
-information....
567
-information....
115
-information....
212
-information....


If I were to sort by rating alone, it would be correct EXCEPT that the original item that I selected (201) would not show up (it has no rating to order on... )

Would it help to think of this like on amazon

view an ipod

customers also bought
- car charger
- headphones
- itunes gift card

Not sure

thanks for the input

sb

 

[r937]

select [red]0[/red], item, information
  from Items
 where item = 201
union all
select [red]B.rating[/red], I.item, I.information
  from BuddyItems as B
inner
  join Items as I
    on I.item = B.buddyitem
 where B.item = 201
order
    by [red]1[/red]

r937.com | rudy.ca