SQL Min(Date) issue with still getting all dates

Question

I am trying to get only records that are the min date of the orderID

I have the following setup:

select
op.OrderID,
op.id,
MIN(op.Date) AS Date
From OrderPermits op
GROUP BY 
Op.OrderId
op.id,
MIN(op.Date) AS Date

Orders has 1 to many order permits.

The problem here is that I am still getting duplicate order Id's when I only want the 1 order ID that is the min(date) for order permits???

Thanks

Answer 1

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY OrderID ORDER BY Date) AS rn
        FROM    orderpermits
        )
SELECT  *
FROM    q
WHERE   rn = 1

This is guaranteed to return one record per OrderID, even in case of ties on MIN(Date).

You may want to read this article:

• SQL Server: Selecting records holding group-wise maximum

Answer 2

You need to specify a WHERE clause:

SELECT
   op.OrderID, op.id, op.Date
FROM
   dbo.OrderPermits op
WHERE
   op.Date = (SELECT MIN(op.Date) FROM dbo.OrderPermits WHERE OrderId = op.OrderId)

Answer 3

Can't try it now but shouln't you remove teh MIN(op.Date) from the group by clause ?

Answer 4

I think you are looking for this:

select op.Date, op.OrderID, op.opid
from (
    select OrderID, MIN(Date) AS MinDate 
    From OrderPermits  
    GROUP BY OrderId 
) opm
inner join OrderPermits op on opm.OrderID = op.OrderID 
    and opm.MinDate = op.Date

Answer 5

Assuming that (except for the multiple returns) your query does what you want...

select
op.OrderID,
op.id,
MIN(op.Date) AS Date
From OrderPermits op
Where op.Date = (Select Min(Date) from OrderPermits) // <-- Add this
GROUP BY
op.OrderID,
op.id,
MIN(op.Date) as Date

Though, if you only want one record returned, you could also do:

Select op.OrderID,
  op.Id,
  op.Date
From OrderPermits op
Where op.Date = (Select MIN(Date) from OrderPermits)

These will both still return multiple results if multiple records share the MIN(Date). I do not know if that is possible with your data or not.