CODEWITHSUNDEEP
sql
Ammar Aziz
Ammar Aziz

Reputation: 3

Get the minimum time from multiple rows

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

Resulting output:

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

I need unique (minimum) time stamps. If there are duplicates, I want just one output.

Upvotes: 0

Views: 56

Answers (2)

Dale K
Dale K

Reputation: 27286

You need to group by like this:

select invlod.lodnum, min(dlytrn.trndte) as trndte
from invlod
inner join dlytrn on invlod.lodnum = dlytrn.lodnum
where invlod.stoloc = (@stoloc) and dlytrn.actcod= 'PALPCK' and dlytrn.oprcod = 'PCK'
group by invlod.lodnum
order by trndte

Upvotes: 0

Charyulu
Charyulu

Reputation: 11

In oracle

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

Upvotes: 1

Related Questions