Forum Moderators: open
I currently have the following data in a database.
id ¦ productID ¦ status ¦ dateIn ¦ Item
-------------------------------------------------------
1 ¦ XTVI00850 ¦ 0 ¦ 2007-01-01 ¦ Stage 7 - Gone Wild / The Fiddle
2 ¦ XTVI00956 ¦ 1 ¦ 2007-01-02 ¦ Stu G Vs. Satsta - Like A Prayer
3 ¦ XTVI00690 ¦ 1 ¦ 2007-01-04 ¦ Di Scala & Crellin - Touch My Body
4 ¦ XTVI00618 ¦ 1 ¦ 2007-01-03 ¦ Neo Cortex - Elements
5 ¦ XTVI00624 ¦ 3 ¦ 2007-01-03 ¦ Kelly Llorena - Stay
6 ¦ XTVI00635 ¦ 3 ¦ 2007-01-06 ¦ Ian Van dahl - Try
7 ¦ XTVI00698 ¦ 2 ¦ 2007-01-07 ¦ Spoiled & Zigo - More & More
8 ¦ XTVI00612 ¦ 1 ¦ 2007-01-07 ¦ Styles & Breeze - Shining
-------------------------------------------------------
Basically this is a list stock that people browse on a website. The status field contains a possible 4 values.
0 = Out Of Stock
1 = In Stock
2 = Re-Stock
3 = Exclusive to this retailer.
At the moment when people are browsing the stock I have it ordering the result by status desc then by dateIn desc. This is ok but we are noticing a drop in requests for the items that are out of stock, since it is putting them items to the bottom of a database that has over 3,000 records in it.
Ideally I would like it order the database by the dateIn field, but if the item has a status of 3, then shove it right to the top of the results.
So the table I have posted looks like this.....
id ¦ productID ¦ status ¦ dateIn ¦ Item
-------------------------------------------------------
6 ¦ XTVI00635 ¦ 3 ¦ 2007-01-06 ¦ Ian Van dahl - Try
5 ¦ XTVI00624 ¦ 3 ¦ 2007-01-03 ¦ Kelly Llorena - Stay
8 ¦ XTVI00612 ¦ 1 ¦ 2007-01-07 ¦ Styles & Breeze - Shining
7 ¦ XTVI00698 ¦ 2 ¦ 2007-01-07 ¦ Spoiled & Zigo - More & More
3 ¦ XTVI00690 ¦ 1 ¦ 2007-01-04 ¦ Di Scala & Crellin - Touch My Body
4 ¦ XTVI00618 ¦ 1 ¦ 2007-01-03 ¦ Neo Cortex - Elements
2 ¦ XTVI00956 ¦ 1 ¦ 2007-01-02 ¦ Stu G Vs. Satsta - Like A Prayer
1 ¦ XTVI00850 ¦ 0 ¦ 2007-01-01 ¦ Stage 7 - Gone Wild / The Fiddle
-------------------------------------------------------
Does anyone know if this is possible?.....
Cheers for you help guys!
You could...
1. Use a scripting approach
2. Two separate queries e.g. "...where status=3..." "...status<3..."
3. Re-assign your status numbers
4. Have column at execute time called "displayPriority" which is derived from dateIn and status in such a way that when displayed ordered by this it is shown exactly in the correct order.
3 sounds like the easiest.
Chris
