Advertisement

07.07.2008 at 07:24PM PDT, ID: 23545292
[x]
Attachment Details

Rank a Vendor by Price from a table with multiple SKUs and muliple Vendors in SQL 2000

Asked by BlueKnight66 in SQL Query Syntax, MS SQL Server, ColdFusion Application Server

Tags: SQL Server 2000

First Kudos to "angellll" for helping with the first attached query.

Building on this query I want to return a ranking based on the price of a sku.  I'd like my fields to include "SKU", "Number of Vendors(other_merchant_count)", and Ranking.  Basically if I had a product with SKU "123456" where "5" other vendors also listed it my price (field in my table is  named "price") when compared to the 5 would rank "1" or "2" or "3" and so on.  I know SQL2K5 has built functions for this but I'm on SQL 2000.  Any help with this will be greatly appreciated.

Thanks in AdvanceStart Free Trial
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
select mp.*
, ( SELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
                  AND imp.sku = mp.sku
              ) other_merchant_count
from merchant_to_product mp
where mp.fk_merchant_id = 373
AND mp.price between 0 AND 300
AND mp.fk_subcategory_id = 89
AND mp.sku IN (SELECT imp.sku
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
              ) 
AND 5 <= ( SELECT COUNT(*)
                 FROM merchant im
                 JOIN merchant_to_product imp
                   ON imp.fk_merchant_id = im.merchant_id
                  AND imp.fk_merchant_id <> 373
                WHERE im.is_active = 1
                  AND imp.sku = mp.sku
              )
[+][-]07.08.2008 at 02:24AM PDT, ID: 21952131

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: SQL Query Syntax, MS SQL Server, ColdFusion Application Server
Tags: SQL Server 2000
Sign Up Now!
Solution Provided By: fesnyng
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628