SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Customize recordset order by specific value(s)		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ofirk
Starting Member

2 Posts
Posted - 01/21/2013 :  09:06:55  Show Profile  Reply with Quote
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 like
Item 0001
Item 0002
Item 0003
Item 0004
Item 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
Flowing Fount of Yak Knowledge

3771 Posts
Posted - 01/21/2013 :  09:26:18  Show Profile  Reply with Quote
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.
Go to Top of Page

ofirk
Starting Member

2 Posts
Posted - 01/21/2013 :  09:31:47  Show Profile  Reply with Quote
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.
Edited by - ofirk on 01/21/2013 09:33:40
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3771 Posts
Posted - 01/21/2013 :  10:02:11  Show Profile  Reply with Quote
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.
Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts
Posted - 01/22/2013 :  21:24:56  Show Profile  Reply with Quote
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.aspx

you 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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000