sql - 从多行中获取最短时间

Question

SELECT
    invlod.lodnum, 
    dlytrn.trndte
FROM  
    invlod
INNER JOIN
    dlytrn ON invlod.lodnum = dlytrn.lodnum
WHERE
    invlod.stoloc = (@stoloc) 
    AND dlytrn.actcod = 'PALPCK' 
    AND dlytrn.oprcod = 'PCK'
ORDER BY 
    dlytrn.trndte

结果输出：

00100370000510204922    1/24/2019 7:28:26 AM    
00100370000510204922    1/24/2019 7:28:44 AM    
00100370000510204939    1/24/2019 7:28:57 AM    
00100370000510204939    1/24/2019 7:29:12 AM    
00100370008030047708    1/24/2019 7:37:01 AM    
00100370008030047708    1/24/2019 7:37:01 AM    

我需要唯一的（最小）时间戳。如果有重复，我只想要一个输出。

标签： sql

Answer 1

在甲骨文中

Select distinct 
invlod.lodnum, 
 min ( dlytrn.trndte) over ( partition by invlod.lodnum) min_trndte
FROM invlod
inner join dlytrn on invlod.lodnum = dlytrn.lodnum
where invlod.stoloc = (@stoloc) and dlytrn.actcod= 'PALPCK' and dlytrn.oprcod = 'PCK'
Order by dlytrn.trndte