Joins and/or Sub queries or Ranking functions

Question

I have a table as follows:

Order_ID	Ship_num	Item_code	Qty_to_pick	Qty_picked	Pick_date
1111	1	1	3000	0	Null
1111	1	2	2995	1965	2021-05-12
1111	2	1	3000	3000	2021-06-24
1111	2	2	1030	0	Null
1111	3	2	1030	1030	2021-08-23
2222	1	3	270	62	2021-03-18
2222	1	4	432	0	Null
2222	2	3	208	0	Null
2222	2	4	432	200	2021-05-21
2222	3	3	208	208	2021-08-23
2222	3	4	232	200	2021-08-25

From this table, I only want to show the rows that has the latest ship_num information, not the latest pick_date information (I was directed to a question like this that needed to return the rows with the latest entry time, I am not looking for that) for an order i.e., I want it as follows

Order_ID	Ship_num	Item_code	Qty_to_pick	Qty_picked	Pick_date
1111	3	2	1030	1030	2021-08-23
2222	3	3	208	208	2021-08-23
2222	3	4	232	200	2021-08-25

I tried the following query,

select order_id, max(ship_num), item_code, qty_to_pick, qty_picked, pick_date
from table1
group by order_id, item_code, qty_to_pick, qty_picked, pick_date

Any help would be appreciated.

Thanks in advance.

I have explained it in this latest question (in the middle), the previous question's top answer was related to returning the rows that had the latest entry "time", I am looking for the max(ship_num) and all rows related to it. — Mupp, Aug 31 '21 at 15:49
You don't really *explain*, @Mupp, but I like I commented, this should be an edit to your original question, *not* a repost — Thom A, Aug 31 '21 at 15:50
Oh, I'm sorry, I actually tried editing first. I will keep that in mind for next time. I am really a new user of stack overflow. Sorry, again. — Mupp, Aug 31 '21 at 15:51
I feel that actually, *the latest ship_num information* is *the ship_num corresponding to the latest pick_date*, am I wrong? In this case, `max()` function could be place on this date, an a 'simple' group by should work — Christophe, Aug 31 '21 at 20:49
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — philipxy, Aug 31 '21 at 20:53
Hi Christophe, in the above example, there are two cases of latest ship_num = 3 for the same order 2222, the pick_dates are different for both, so a max() on pick_date will not work for me — Mupp, Sep 01 '21 at 19:06

score 1 · Answer 1 · edited Aug 31 '21 at 20:43

1

You can get this using the DENSE_RANK().

Query

;with cte as (
    select rnk = dense_rank()
       over (Partition by order_id order by ship_num desc)
        , *
    from table_name
)
Select *
from cte
Where rnk =1;

edited Aug 31 '21 at 20:43

Charlieface

52,284
6
19
43

answered Aug 31 '21 at 15:42

Ullas

11,450
4
33
50

could you please explain your choice of what appear to me a complex query regarding the question? And there is no date in this query, where as I understand our friend is looking for the *latest* ship_num... Am I wrong? I have to recognize that I'm surprised by the answers, I would certainly not have look for the answer in this kind of direction, and I'm wondering if I really understood the question :-/ Thanks for details =) – Christophe Aug 31 '21 at 20:55
@Christophe: While "latest ship num" is a bit confusing, I think it becomes clear in the request that the "greatest ship num" is meant. The OP explicitly mentions "not the latest pick_date", they use max(ship_num) themselves, and the expected result shown confirms that. – Thorsten Kettner Sep 01 '21 at 19:18

score 1 · Accepted Answer · answered Aug 31 '21 at 15:42

Using max(ship_num) is a good idea, but you should use the analytic version (with an OVER clause).

select *
from
(
  select t.*, max(ship_num) over (partition by order_id) as orders_max_ship_num
  from table1 t1
) with_max
where ship_num = orders_max_ship_num
order by order_id, item_code;

Joins and/or Sub queries or Ranking functions

2 Answers2

Linked