Ordering select results by column data...

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Ordering select results by column data...

Author

Topic

gittie
Starting Member

3 Posts

Posted - 2010-02-05 : 06:35:03

Right, I'm sure there's a simple way of doing this, but hours of trial & error haven't got me close.

Basically, I'm building an eCommerce site which lets the user select a colour scheme which will then return the items matching the colour scheme first, followed by the rest of the items also.

The "items" table has 3 'colour scheme' columns, each set as an integer (i.e. 1=black, 2=white, 3=red etc) matching the items colour. When they select a colour scheme, it sets a session (i.e. $_SESSION["colourscheme"] = 1 for black).

Put simply, I want the results to display something like as follows:

Item 1: Black item a
Item 2: Black item b
Item 3: Black item c
Item 4: Red item a
Item 5: White item a

This way, the items they are most likely interested in are displayed first but all other items will also display.

Please help!

Wayne

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2010-02-05 : 07:10:15

Do you have sample data and structures of your tables? Please post them (or close examples of what you are after) and you will get more help.

There are many ways to order data, but it would depend on how you store your data for each customer. If you do not want to post examples, then the best I can suggest is to look up row_number() or Rank().

gittie
Starting Member

3 Posts

Posted - 2010-02-05 : 07:29:47

The table structure for the items is as follows:

ITEM_KEY int(12)
ITEM_CAT1 int(12)
ITEM_CAT2 int(12)
ITEM_CAT3 int(12)
ITEM_REF varchar(10)
ITEM_NAME varchar(100)
ITEM_DESC text
ITEM_PRICE decimal(6,2)
ITEM_RRP decimal(6,2)
ITEM_IMG varchar(100)
ITEM_URL varchar(200)
ITEM_KEYWORDS text
ITEM_VARIATIONS enum('Y','N')
ITEM_COLOUR1 int(12)
ITEM_COLOUR2 int(12)
ITEM_COLOUR3 int(12)
ENG_FLAG enum('Y','N')
DISPLAY_FLAG enum('Y','N')

Each item can be flagged as up to 3 different colour schemes (ITEM_COLOUR1, ITEM_COLOUR2 & ITEM_COLOUR3)
As an example;
"ITEM_COLOUR1" may be 1 and "ITEM_COLOUR2" may be 2 meaning that the item is black (1) and white (2)

I want it that if someone has selected White as a colour scheme, the select query will check the ITEM_COLOUR1, ITEM_COLOUR2 & ITEM_COLOUR3 columns for items that are flagged white (2) and order these first, and then any items that are not white after.

If you need any other info, please let me know.

I'm going to have a good look into row_number() & Rank() now...

Kristen
Test

22859 Posts

Posted - 2010-02-05 : 07:43:40

I normally do this with:


SELECT ...
FROM ...
ORDER BY
    CASE WHEN SomeColumn = 'Black' THEN 1 ELSE 2 END,
    SomeOtherSortColumn

so in your case, if Black=1, then perhaps something like:


SELECT ...
FROM ...
ORDER BY
    CASE WHEN ITEM_COLOUR1 = 1 OR ITEM_COLOUR2 = 1 OR ITEM_COLOUR3 = 1 THEN 1 ELSE 2 END,
    SomeOtherSortColumn

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 07:43:47

order by case when 2 in (item_colour1,item_colour2,item_colour3) then 0 else 1 end

Madhivanan

Failing to plan is Planning to fail

gittie
Starting Member

3 Posts

Posted - 2010-02-05 : 07:55:04

Thanks Kristen - that works a treat!
So simple when you know how...

You're all stars!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources