CODEWITHSUNDEEP
sqlsql-server
Aylin
Aylin

Reputation: 177

how to get the largest of values in group by sql

I have one table, table contain OperationDate,LotID,NewStatusID,opeTypeId

I want to show OperationDate(top1) for every lot.

my query like this:

      select a.OperationDate,a.LotID
  FROM  [dbo].[MMwLotOperations]   a
  where   a.LotID in ('L705816008','L704511001')
    GROUP by a.LotID,a.OperationDate 
 having  a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status'

this query returns these data:

enter image description here

L705816008 lot's NewStatusID returns 1 data

but L704511001 lot's NewStatusID returns 2 data

now I want to show the biggest OperationDate for every lot. how can do this?

could you help me please?

Thanks.

Upvotes: 0

Views: 86

Answers (4)

Vibhav Bhadauriya
Vibhav Bhadauriya

Reputation: 1

use distinct keyword to select a.LotID

select a.OperationDate,distinct(a.LotID)
FROM  [dbo].[MMwLotOperations]   a
 where   a.LotID in ('L705816008','L704511001')
GROUP by a.LotID,a.OperationDate 
having  a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status'

i hope this will work for you

Upvotes: 0

Ifham
Ifham

Reputation: 59

 select MAX(a.OperationDate) as max_date,a.LotID
  FROM  [dbo].[MMwLotOperations]   a
  where   a.LotID in ('L705816008','L704511001')
    GROUP by a.LotID,a.OperationDate
 having  a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status'

Try this statement to get the max date

Upvotes: 0

TriV
TriV

Reputation: 5148

You could use MAX to get the largest date and remove a.OperationDate from Group By

select MAX(a.OperationDate),a.LotID
FROM  [dbo].[MMwLotOperations]   a
where   a.LotID in ('L705816008','L704511001')
        AND a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status' 
GROUP by a.LotID

NOTE: Having specifies a search condition for a group or an aggregate. In your case, put a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status' to where condition

Upvotes: 3

Vinoth_S
Vinoth_S

Reputation: 1530

select * from 
(   select a.OperationDate,a.LotID,ROW_NUMBER() OVER(partition by a.LotID ORDER BY a.OperationDate desc) as rn
  FROM  [dbo].[MMwLotOperations]   a
  where   a.LotID in ('L705816008','L704511001')
    GROUP by a.LotID,a.OperationDate 
 having  a.NewStatusID ='Assigned' AND a.opeTypeId = 'Status' ) where rn =1

Upvotes: 0

Related Questions