Get type field for first contract ever made

user_10602597 0
Viewed 30

I have table for of contracts like this

CREATE TABLE contracts 
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    contractor_id INT NOT NULL,
    date DATETIME NOT NULL,
    type VARCHAR(50) NULL
)

I want to know type on first contract ever made for contractor.

I have query below, but cannot get type

SELECT contractor_id, MIN(date) AS min_date 
FROM contracts c 
GROUP BY contractor_id

Thank you for your help

sql
2 Answers

You're on the right track. Your query groups the contracts table on contractor_id and selects the minimum date for that contractor.

To get the corresponding type, you need to join the table back onto itself using multiple conditions on the join.

;WITH subq AS (
    SELECT 
        contractor_id, MIN(date) AS min_date
    FROM 
        contracts c
    GROUP BY 
        contractor_id
)
SELECT 
    s.contractor_id, c.type
FROM 
    subq s
LEFT JOIN 
    contracts c ON s.contractor_id = c.contractor_id AND s.min_date = c.date
user_71472550

This would normally be solved using window functions:

SELECT c.*
FROM (SELECT c.*,
             ROW_NUMBER() OVER (PARTITION BY contractor_id ORDER BY date) as seqnum
      FROM contracts c
     ) c 
WHERE seqnum = 1;

With the right indexes, a correlated subquery is often the fastest method:

select c.*
from contracts c
where c.date = (select min(c2.date)
                from contracts c2
                where c2.contract_id = c.contract_id
               );

The appropriate index would be on contracts(conract_id, date).

user_11440350
Related