Grouping at high level

Question

Customer_Name      Itemcode Order_Number    Quantity ord    u_es    Avail. Fulfillment%
-----------------------------------------------------------------------------------------------------
oracle blaionuel   1019885  F130238518001   1           CET     0            0.00
oracle blaionuel   1132006  F130238518001   1           CET     1            100.000
oracle blaionuel   1016964  F130238518001   1           CET     0            0.00

I want to achieve an t-sql query that will introduce another column [Fulfilment% Order Level], which takes the least Fulfillment% of the 3 rows . I am using Mssql 2008 i.e

Customer_Name      Itemcode Order_Number    Quantity ord    u_es    Avail. [Fulfilment% Order Level]
-------------------------------------------------------------------------------------------------
oracle blaionuel   1019885  F130238518001   1           CET     0            0.00

what about the itemcode and the u_es? how do you pick those values instead of the other ones? — mucio
– mucio, Commented Dec 15, 2014 at 11:28
I guess tp asks for the row with the lowest Avail. Fulfillment%. But that's a tie in this case, and I don't know if that means return 2 rows, or how to pick one single row... — jarlh
– jarlh, Commented Dec 15, 2014 at 11:30

Gordon Linoff · Accepted Answer · 2014-12-15 11:59:34Z

1

I think you are looking for row_number():

select t.*
from (select t.*,
             row_number() over (partition by customer_name, order_number order by [Fulfillment%] asc) as seqnum
      from table t
     ) t
where seqnum = 1;

edited Dec 15, 2014 at 11:59

answered Dec 15, 2014 at 11:30

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Pரதீப் Over a year ago

looks like OP need lowest fulfilment% and there is a tie

Gordon Linoff Over a year ago

@NoDisplayName . . . When there is a tie, I assume one of the rows can be chosen arbitrarily.

Collectives™ on Stack Overflow

Grouping at high level

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related