# Ordered items in database?

**URL:** <https://forum.xojo.com/t/ordered-items-in-database/9556>\
**Category:** Databases\
**Created:** [July 16, 2013, 9:39am UTC](https://forum.xojo.com/t/ordered-items-in-database/9556 "2013-07-16T09:39:52Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [July 16, 2013, 9:39am UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/1 "2013-07-16T09:39:52Z")

</div>

Hi,

I’m trying to save some items in a database but am a bit flumoxed on the how.

I deal with several Items (“proteases”) which each have multiple replacement rules. The order in which these rules are executed is important, eg “add a space after all K except if K is followed by P” (to use a simple one)

Replace “K” with "K "  
Replace “K P” with “KP”

How would I save this in a database while keeping the order for each item? I’m somehow under the impression that the order isn’t fix (for  each etc).

Thanks

Markus

---

<div class="post-metadata">

**Author:** ![Beatrix\_Willius](https://forum.xojo.com/user_avatar/forum.xojo.com/beatrix_willius/32/282_2.png) [@Beatrix\_Willius](https://forum.xojo.com/u/Beatrix_Willius)\
**Post date:** [July 16, 2013, 9:44am UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/2 "2013-07-16T09:44:15Z")

</div>

You would need an additional column for a formula or a copy of your original column where you make your replacements. Then you do an order by the original column.

---

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [July 16, 2013, 12:25pm UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/3 "2013-07-16T12:25:37Z")

</div>

Thanks, but I might not have expressed it well enough.

I have some items which are stored in a table PROTEASES

Trypsin  
HS-Trypsin  
Chymotrypsin  
Elastase

etc.

I have a table RULES which contains two columns (Target and Replacement) with the replacement rules.

“K” "K " \<- ID1  
“K P” “KP” \<- ID2  
“R” "R " \<- ID3  
“R P” “RP” \<- ID4  
“K K” “KK” \<- ID5  
“R R” “RR” \<- ID6  
 etc

Each protease can have multiple rules, each rule can occur in multiple Proteases, so we have an n:n relationship and need to use a link table PROTEASE-RULES

PROTEASE\_ID RULES\_ID

Now lets say (don’t look at the actual content) for Trypsin the rules are (in this order) 1,2,3,4,5,6  
but for Chymotrypsin they are 3,4,1,2  
and for Elastase they are 3,1, 8

I know I can get the rules for each protease from the link table but where do I store the order information? I could store it as a string in PROTEASES and parse it but that seems inelegant. I’m sure there is a better solution somewhere 

---

<div class="post-metadata">

**Author:** ![Paul\_Lefebvre](https://forum.xojo.com/user_avatar/forum.xojo.com/paul_lefebvre/32/3152_2.png) [@Paul\_Lefebvre](https://forum.xojo.com/u/Paul_Lefebvre)\
**Post date:** [July 16, 2013, 12:36pm UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/4 "2013-07-16T12:36:56Z")

</div>

If I understand you correctly, it looks like you can just add a SortOrder column to PROTEASE-RULES. Then when you need to know the rules, you fetch them in sorted order:

`SELECT r.Target, r.Replacement FROM PROTEASE-RULES pr 
INNER JOIN PROTEASES p ON pr.PROTEASE_ID = p.PROTEASE_ID 
INNER JOIN RULES r ON pr.RULES_ID = r.RULES_ID
WHERE p.Name = "Trypsin" ORDER BY pr.SortOrder`

---

<div class="post-metadata">

**Author:** ![Markus\_Winter](https://forum.xojo.com/user_avatar/forum.xojo.com/markus_winter/32/144_2.png) [@Markus\_Winter](https://forum.xojo.com/u/Markus_Winter)\
**Post date:** [July 16, 2013, 12:55pm UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/5 "2013-07-16T12:55:03Z")

</div>

Thanks Paul - I just came up with that too. Silly me, but sometimes it just helps laying out the problem for others to find the solution.

Looking forward to the webinar tonight 🙂

---

<div class="post-metadata">

**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\
**Post date:** [October 29, 2020, 4:45pm UTC](https://forum.xojo.com/t/ordered-items-in-database/9556/6 "2020-10-29T16:45:08Z")

</div>


