我需要从第一个实例返回所有字段 - SQL

I am trying to retrieve the all fields from the first instance of an item in a sales order table.

我正在尝试从销售订单表中的项目的第一个实例中检索所有字段。

    Item    Order Date  Order Qty
    Item1    17/09/2013  1
    Item1    20/09/2013  2
    Item2    16/09/2013  2
    Item2    17/09/2013  1
    Item3    20/09/2013  3
    Item3    20/09/2013  2
    Item3    20/09/2013  1

The records in bold are the ones I am after. My staging table is already sorted by order date (and order qty later on).

粗体的记录是我追求的。我的临时表已按订单日期排序(稍后订购数量)。

Select DISTINCT and Select GROUP are not suitable as I need to return all the field (there are more that are not shown) and I do not want the total order quantity.

选择DISTINCT和Select GROUP不合适,因为我需要返回所有字段(还有更多未显示的字段),我不想要总订单数量。

I would greatly appreciate the help.

我非常感谢你的帮助。

3 个解决方案

#1

Here's one using FIRST function.

这是一个使用FIRST功能。

select item,
      min(orderdate) keep(dense_rank first order by orderdate, orderqty desc),
      min(orderqty) keep(dense_rank first order by orderdate, orderqty desc)
from items
group by item;

Sample here.

#2

If you want to get first occurrence without any order use the below. But if you want to order based on date and quantity, add that in the order by class of row_number function.

如果您想在没有任何订单的情况下首次出现,请使用以下内容。但是如果要根据日期和数量进行排序,请按row_number函数类的顺序添加。

 Select Item, Order_Date, Order_Qty from 
(select Item, Order_Date, Order_Qty, 
row_number() over(partition by Item order by null) rn
from table1) a where rn = 1;

#3

select t.*
from your_table t
inner join
(
   select x.Item, min(x."Order Date") as mod
   from 
   (
      select Item, "Order Date", max("Order Qty")
      from your_table
      group by Item, "Order Date"
   ) x
   group by x.Item       
) y on y.item = t.item and y.mod = t."Order Date"

#1