CODEWITHSUNDEEP
sqlsql-servert-sqljoin
moonman4
moonman4

Reputation: 318

Select Rows with min value for each group

I have a database with a table that look like this:

Date Container ID
1/1 1
2/2 1
3/3 1
4/4 2
5/5 2
6/6 3
7/7 3

Container ID is a foreign key to another table and the containers are effectively named groups that elements can be part of.

I'm looking to write a query that gets the earliest date for each Container ID like this:

Earliest Date Container ID
1/1 1
4/4 2
6/6 3

Is there some way I can join on Container ID to make this happen or am I on the wrong path with joins?

Upvotes: 2

Views: 5953

Answers (4)

John Cappelletti
John Cappelletti

Reputation: 81970

Just another option using WITH TIES

Select top 1 with ties *
 From  YourTable
 Order By row_number() over (partition by ContainerID Order by Date)

Upvotes: 1

nbk
nbk

Reputation: 49375

But the best would be save dates as date column. The you can use all function for dates

CREATE TABLE table1 (
  [Date]  varchar(10),
  [Container ID] INTEGER
);

INSERT INTO table1
  ([Date], [Container ID])
VALUES
  ('1/1', '1'),
  ('2/2', '1'),
  ('3/3', '1'),
  ('4/4', '2'),
  ('5/5', '2'),
  ('6/6', '3'),
  ('7/7', '3');
GO

SELECT MIN([Date]), [Container ID] FROM table1 GROUP BY [Container ID]
GO

(No column name) | Container ID
:--------------- | -----------:
1/1              |            1
4/4              |            2
6/6              |            3

db<>fiddle here

Upvotes: 3

Nick
Nick

Reputation: 3845

I have not used a date column but you can adjust yours to suit

CREATE TABLE DAta (Date_example VARCHAR(50), ContainerID INT)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1a', 1)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2a', 1)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3a', 1)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1b', 2)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2b', 2)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3b', 2)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('1c', 3)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('2c', 3)

INSERT INTO DAta (Date_example, ContainerID)
VALUES ('3c', 3)

The resulting table is:

+---------------+-------------+
| Date_example  | ContainerID |
+---------------+-------------+
| 1a            |           1 |
| 2a            |           1 |
| 3a            |           1 |
| 1b            |           2 |
| 2b            |           2 |
| 3b            |           2 |
| 1c            |           3 |
| 2c            |           3 |
| 3c            |           3 |
+---------------+-------------+

SOLUTION:

SELECT *
FROM (
    SELECT DATE_EXAMPLE
        ,CONTAINERID
        ,ROW_NUMBER() OVER (
            PARTITION BY CONTAINERID ORDER BY CONTAINERID
            ) ROW_NUM
    FROM DATA
    ) A
WHERE ROW_NUM = 1

OUTPUT:

+---------------+--------------+---------+
| date_example  | containerID  | row_num |
+---------------+--------------+---------+
| 1a            |           1  |       1 |
| 1b            |           2  |       1 |
| 1c            |           3  |       1 |
+---------------+--------------+---------+

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You want group by and min():

select id, min(date)
from t
group by id;

As a note: You could also use join, but it is a bit more arcane:

select t.*
from t left join
     t t2
     on t.id = t2.id and t2.date < t.date
where t2.id is null;

The left join is matching to rows that have a smaller date. The smallest date (per id) is the one with no matching rows.

Upvotes: 0

Related Questions