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 |
|
ofirk
Starting Member
2 Posts |
 Posted - 2013-01-21 : 09:06:55
|
| Hi everyone!I have an online store webapp which I wrote in classic ASP and SQL and I need help with the items order results.The SQL statement set to order the recordset results of the items by the item no, means for example the order likeItem 0001 Item 0002Item 0003Item 0004Item 0005 and etc,.Sometimes I have products that are from the same "family" aka same item in different sizes or colors that I want to show in a row (but the item no is not contentious).So for this example I have items 0001-0005 that showed in a row all together correctly, but I have similar item that I want to show after item "0005" but the item number of this item is "0030", so for now the user see it just after 23 items (0006-0029) and then item "0030" shows up which makes the whole items look like a mess.What I want to do is to keep the order as it is by item no, but after item 0005 to show item 0030 as it's very similar item, and then to return back the recordset pointer to continue load the results from the place it's stopped (0006).Once the recordset will get to 0030 I want it to skip this record as it's already visible to the user and already loaded before.I thought to add a column to the items table named "next_item" and there I can mention the next item no. I want to show.So for the rows 0001-0004 I can leave this column field blank and the recordset will load the results as original, and just for item 0005 I can insert the value 0030.Can someone help me with this issue? Or maybe have a better idea how to solve this issue?Thanks alot!Ofir |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-21 : 09:26:18
|
| If you don't already have a column in your data that indicates the family that each item belongs to you should consider adding that. It may even be advisable to create a separate table that has item and family as the two columns to make the data normalized. Regardless, if you had three columns - Item, ItemNo, ItemFamily - you can then order by ItemFamily,ItemNo to get the result ordered like you described. |
 |
|
|
ofirk
Starting Member
2 Posts |
Posted - 2013-01-21 : 09:31:47
|
Hi James, thank you for your reply.The Family is actually something that not mentioned in the tables, please see example of part of my items table, it may help to understand what I need to do:ITEM_NUMBER | DESCRIPTION | PRICE | NEXT_ITEM |-----------------------------------------------0001 Item 0001 1.5-----------------------------------------------0002 Item 0002 2-----------------------------------------------0003 Item 0003 1-----------------------------------------------0004 Item 0004 3-----------------------------------------------0005 Item 0005 7 0030-----------------------------------------------....-----------------------------------------------0030 Item 0030 3 As you can see I don't have a value that indicate families as it's too much work to do now for all the items.Do you have any idea that can help me ho to jump to item "0030" after showing item "0005" and then to return back to item "0006" and etc,.?Thanks alot!Ofir. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-21 : 10:02:11
|
| I am sorry to say that the data set up the way you showed is designed only for processing by procedural programs; it is very unweildy for a set-based environment like T-SQL/SQL Server. I can think of some rather convoluted ways to order them in T-SQL, but none that I would want to present here.My recommendation would be to add a column to the table to store the family identifier, then use a procedural environment such as .Net/C# to read the data and populate the new column. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-01-22 : 21:24:56
|
| Assuming you have next_item, I think this is a variation on the classic parent/child hierachy where next_item indicates a parent.There are plenty of articles on how to extract and traverse hierachies.http://www.sqlservercentral.com/Forums/Topic796015-338-1.aspxyou should be able to use the info in the link (and many others like) it to work out parent/child levels that you can combine to get the correct order. 2008 looks like it might have more tools for this as well:http://www.sqlservercentral.com/articles/SQL+Server+2008/62204/Personally I'd hold the parent on the child so you can have multiple family members, not the other way around which limits you to one.Of course it may be that you do want a family ID instead of a "related product" ID and that's different again.However you also state that you don't have any relationship in the data (assume then your example in post 3 is the proposal) then I can't see how you are supposed to know that 30 is related to 5 or any other similar examples. |
|
|
|
|
|
|
|
|
Customize recordset order by specific value(s)