How to get lowest ranked record

[PJSWEAT]

I am having difficultly working the logic out for this -

I have this table:

P S D
StoreA Widget1 1 1 1
StoreB Widget1 3 5 2
StoreC Widget1 2 3 3
StoreD Widget1 2 2 4
StoreA Widget2 3 3 1
StoreB Widget2 2 3 2
StoreC Widget2 2 3 3
StoreD Widget2 1 2 4
StoreA Widget3 1 2 1
StoreB Widget3 2 5 2
StoreC Widget3 2 2 3
StoreD Widget3 2 2 4
StoreA Widget4 3 3 1
StoreB Widget4 2 2 2
StoreC Widget4 2 3 3
StoreD Widget4 1 3 4

I need to create a table from the above source table that tells me which store to buy each widget (1,2,3,4...there are 100s of widgets) from based first on lowest price (first column) if that is the same at more than one store for the widget then the next deciding factor would be lowest shipping cost (second column) for the widget if that’s the same at more than one store then the least amount of days (third column) it takes to ship would break the tie. I am on SQL 2000 and 2005 boxes. There can be many more than 4 stores and many more than 4 widgets. For each column the value of 1 is the most favorable and the value of 4 is the least favorable

Thanks so much for taking the time!

 

[PatriciaObreja]

Try like this:

select * from Table as a
where p =
(
select min(p) from Table as b where b.store = a.store
)
and s =
(
select min(s) from Table as c where c.store = a.store
)
and d =
(
select min(d) from Table as d where d.store = a.store
)

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[ESquared]

That query isn't going to work, PatriciaObreja, because the winning row may not have the lowest values for all columns. A price of 1 and shipping days of 10 will win over a price of 2 and shipping days of 5. No one record then has price 1 and days 5, so that widget returns no results.

A correlated subquery is the "easiest" way to do this, but may not perform the best. But it will get us started.

SELECT
  Widget,
  Store
FROM
   WidgetStorePriceInfo P
WHERE
   Store = (
      SELECT Top 1 Store
      FROM WidgetStorePriceInfo L
      WHERE P.Widget = L.Widget
      ORDER BY Price, Shipping, DeliveryDays
-- Price * @OrderQty + Shipping, DeliveryDays -- this could work, too
   )

Then, if you want to convert that to a derived table you can. For small result sets the correlated subquery will be better, for the entire table if it's very large, the derived table will kick the C-S's pants off.

Last you can do this funky thing which you probably shouldn't but will probably outperform everything else by close to double:

SELECT
  Widget,
  Store = Convert(varchar(100), Substring(Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget)), 25, 100))
FROM
   WidgetStorePriceInfo P
GROUP BY
   Widget

And you can either join that back to the main table or do more twisted and confusing stuff that if you put in your production database probably needs a full primer on what's going on in the comments, two different versions that perform almost identically:

SELECT
  Widget,
  Store = Convert(varchar(100), Substring(Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget)), 25, 100)),
  Price = Convert(int, Substring(Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget)), 1, 8)),
  Shipping= Convert(int, Substring(Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget)), 9, 8)),
  DeliveryDays= Convert(int, Substring(Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget)), 17, 8))
FROM
   WidgetStorePriceInfo P
GROUP BY
   Widget

SELECT
   Widget,
   Store = Convert(varchar(100), Substring(Packed, 25, 100),
   Price = Convert(int, Substring(Packed, 1, 8),
   Shipping = Convert(int, Substring(Packed, 9, 8),
   DeliveryDays = Convert(int, Substring(Packed, 17, 8)
FROM (
   SELECT
     Widget,
     Packed = Min(Convert(binary(8), Price) + Convert(binary(8), Shipping) + Convert(binary(8), DeliveryDays) + Convert(varbinary(100), Widget))
   FROM
      WidgetStorePriceInfo P
   GROUP BY
      Widget
  ) X

Your price is probably not an integer, so you'd need to massage that to handle whatever data types you're using. Decimal is most likely, and you can't convert that to binary directly and get any meaningful sort order out of it, so you'd have to convert it to int or zero-padded character first, and then back to decimal. But it's possible and at the cost of some CPU works great (which CPU is more than recovered back by only hitting the data table half the times).

Using this packing method takes some knowledge of server internal data type storage and possible conversions, but is quite fun as it accomplishes in a single hit on the table what otherwise takes at least two with a join between them.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

[sub]The best part about anything that has cheese is the cheese.[/sub][/color]

 

[gmmastros]

Patricia,

If I understand PJSWEAT correctly, your query will not return the correct records.

For example, your query incorrectly reports that Widget4 should be bought at StoreB, eventhough StoreD has a P of 1.

Patricia, there is a 'trick' you can do to get this to work which does not involve any subqueries, but does involve a derived table. The particular trick I have in mind makes use of the fact that the stored are already ranked in value for each column. I'll probably wait an hour or so to post this solution which may (hopefully) give you time to 'try again'.

I am on SQL 2000 and 2005 boxes.

I'm not sure what this means. Does this mean that a SQL2005 solution is acceptable, even if it won't work on sql2000?

If so.... take a look at this...

[green]-- Create dummy data[/green]
Declare @Temp Table(Store Varchar(20), Product VarChar(20), P Int, S int, D int)

Insert Into @Temp Values('StoreA','Widget1',    1,    1,    1)
Insert Into @Temp Values('StoreB','Widget1',    3,    5,    2)
Insert Into @Temp Values('StoreC','Widget1',    2,    3,    3)
Insert Into @Temp Values('StoreD','Widget1',    2,    2,    4)
Insert Into @Temp Values('StoreA','Widget2',    3,    3,    1)
Insert Into @Temp Values('StoreB','Widget2',    2,    3,    2)
Insert Into @Temp Values('StoreC','Widget2',    2,    3,    3)
Insert Into @Temp Values('StoreD','Widget2',    1,    2,    4)
Insert Into @Temp Values('StoreA','Widget3',    1,    2,    1)
Insert Into @Temp Values('StoreB','Widget3',    2,    5,    2)
Insert Into @Temp Values('StoreC','Widget3',    2,    2,    3)
Insert Into @Temp Values('StoreD','Widget3',    2,    2,    4)
Insert Into @Temp Values('StoreA','Widget4',    3,    3,    1)
Insert Into @Temp Values('StoreB','Widget4',    2,    2,    2)
Insert Into @Temp Values('StoreC','Widget4',    2,    3,    3)
Insert Into @Temp Values('StoreD','Widget4',    1,    3,    4)

[green]-- Query starts here[/green]
Select *
From   (
       Select *, Row_Number() Over (Partition By Product Order By P,S,D) As Rank
       From   @Temp
       ) As A
Where  Rank = 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PJSWEAT]

George your solution was perfect!

Thanks for everybody's help!

 

[PatriciaObreja]

Row_Number() is a new feature of SQL Server 2005. Sorry I did not know about it, because I've used only SQL Server 2000.

My previous query was wrong indeed.

I enetered the values in a table, and ordered by store to be easier to read them:
id store widget p s d
----------- ---------- ---------- ----------- ----------- -----------
1 a 1 1 1 1
3 a 2 3 3 1
5 a 3 1 2 1
6 a 4 3 3 1
4 b 2 2 3 2
2 b 1 3 5 2
11 b 3 2 5 2
14 b 4 2 2 2
15 c 4 2 3 3
12 c 3 2 2 3
9 c 2 2 3 3
7 c 1 2 3 3
8 d 1 2 2 4
10 d 2 1 2 4
13 d 3 2 2 4
16 d 4 1 3 4

We want to get this data:
id store widget p s d
----------- ---------- ---------- ----------- ----------- -----------
1 a 1 1 1 1
14 b 4 2 2 2
12 c 3 2 2 3
10 d 2 1 2 4

I can get this data using this query:
select * from Table3 as a
where

(p =
(
select min(p) from Table3 as b where b.store = a.store
)
and
(select count(*) from Table3 as c
where c.store = a.store
and p = (select min(p)
from Table3 as b where b.store = a.store)) = 1)

OR

(
p =
(
select min(p) from Table3 as b where b.store = a.store
)
and s =
(
select min(s) from Table3 as c where c.store = a.store
)
and
(select count(*) from Table3 as c
where c.store = a.store
and p = (select min(p)
from Table3 as b where b.store = a.store)
and s = (select min(s)
from Table3 as c where c.store = a.store)) = 1
)

OR

(
p =
(
select min(p) from Table3 as b where b.store = a.store
)
and s =
(
select min(s) from Table3 as c where c.store = a.store
)
and d =
(
select min(d) from Table3 as d where d.store = a.store
)
)

order by Store

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[gmmastros]

Patricia,

Your expected results are not right. Let me try to explain.

The purpose of this query is to determine which store is the best one to purchase from.

P represents Price. S represents shipping cost. D represents the number of days. Each category is ranked based on a scale of 1 to 4 where 1 is best and 4 is worst.

We can use this information to our advantage. If the question had been....

How do I get all the data in a row where rank is lowest for each widget.

Sample Data
[tt][blue]
Store Widget Rank
----- ----------- -----------
a 1 111
a 2 331
a 3 121
a 4 331
b 2 232
b 1 352
b 3 252
b 4 222
c 4 233
c 3 223
c 2 233
c 1 233
d 1 224
d 2 124
d 3 224
d 4 134
[/blue][/tt]

The answer to this question would have been a simple derived table, right?

Select Table3.*
From   Table3
       Inner Join (
         Select Widget, Min(Rank) As Rank
         From   Table3
         Group By Widget
         ) As A
         On Table3.Widget = A.Widget
         And Table3.Rank = A.Rank

Well... like I said earlier. You can use the P, S & D columns to our advantage because we can manufacture a rank column from the original data.

[tt][blue]
Select *, (P*100 + S*10 + D) As Rank
From Table3
[/blue][/tt]

So, we can construct a query that manufactures a rank column for us, and then use it in the query. Of course, the query is not the 'prettiest' code I've ever seen, but it does work.

Select Table3.*
From   Table3
       Inner Join (
         Select Widget, 
                Min(P*100 + S*10 + D) As Rank 
         From   Table3
         Group By Widget
         ) As A
         On Table3.Widget = A.Widget
         And (Table3.P*100 + Table3.S*10 + Table3.D) = A.Rank

Obviously PJSWEAT has already found an acceptable solution to the problem. The only reason I mention this method is because it shows an alternative method for returning the same data. When writing queries, it is vitally important that you get the correct results. Almost as important is the execution time of the query. I couldn't begin to tell you how many queries I've seen that work properly but execute slowly.

Patricia, I've seen some of your other posts in this forum. Many of them are very good posts. I would encourage your continued participation here because I think you have many good ideas that can be shared with others in this community. Please do not interpret any of this as an insult. It's not. Consider this a learning experience, because that is the spirit in which I've written this.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

If my expected resutls are not right it means that I misunderstood the problem from the very begining.

Thanks for encouraging me!

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium