CODEWITHSUNDEEP
sqldaterecordmin
proudestWarehouse89
proudestWarehouse89

Reputation: 19

SQL select minimum date from same column

I'm trying to write a query based on accounts and their contracts. The table has all contracts for each account, whether the contract is active, expired, etc. I want the query to only bring back the contract with earliest start date per account, so only one row for each account. However i don't know the status of the earliest contract for each account. Some might have active, some might have pending. I run into the problem now where it brings back multiple records for each account if the contract status is in the list i specify. Simple sample code below:

Select t.account, t.contract, t.status Min(t.start_date)
From table t
where t.status in ('Active','Countersigned','Pending')

Upvotes: 0

Views: 2284

Answers (3)

Paul A Jungwirth
Paul A Jungwirth

Reputation: 24541

If your database supports it (e.g. Oracle, Postgres, SQL Server, but not MySQL or SQLite), you can use Window Functions. For instance, you can rank your contracts within each account by starting_at:

SELECT *, rank() OVER (PARTITION BY account_id ORDER BY starting_at ASC) AS rank
FROM   contracts

Then you can use that in a subquery to join to accounts and only take contracts with a rank of 1. You'll need to put it in a subquery, because unfortunately (in Postgres at least) you can't use window functions inside WHERE. So this won't work:

SELECT *, rank() OVER (PARTITION BY account_id ORDER BY starting_at ASC) AS rank
FROM   contracts
WHERE  rank = 1

but this will:

SELECT *
FROM (SELECT *, rank() OVER (PARTITION BY account_id ORDER BY starting_at ASC) AS rank
      FROM   contracts) x
WHERE rank = 1

Note you can easily add filtering by status, etc. to any of these queries.

Upvotes: 1

Vland
Vland

Reputation: 4262

a solution that works if you don't have multiple contracts for each account on the same MIN(date) (in that case you'd get multiple rows for each account and you should decide which of these N contracts you want to see, I can't decide for you)

SELECT t.*
FROM (
    Select t.account, Min(t.start_date) AS MinDate
    From table t
    where t.status in ('Active','Countersigned','Pending')
    GROUP BY t.account
) AS t2
INNER JOIN table t ON t.account = t2.account AND t.start_date = t2.MinDate

Upvotes: 0

Jayvee
Jayvee

Reputation: 10875

This should work:

select account, contract, status, MinDate
from
(
Select t.account, t.contract, t.status, t.start_date,
Min(t.start_date) over(partition by t.account) MinDate
From table t
where t.status in ('Active','Countersigned','Pending')
) x
where start_date=MinDate

Upvotes: 0

Related Questions