CODEWITHSUNDEEP
sqlsql-serversql-server-2005
user380432
user380432

Reputation: 4779

SQL Min(Date) issue with still getting all dates

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

Upvotes: 1

Views: 5810

Answers (5)

marc_s
marc_s

Reputation: 755157

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)

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425713

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:

Upvotes: 4

AllenG
AllenG

Reputation: 8190

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.

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171529

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

Upvotes: 2

Edelcom
Edelcom

Reputation: 5058

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

Upvotes: -1

Related Questions