Picking the largest or smallest from data pairs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Picking the largest or smallest from data pairs

Author

Topic

JubJub
Starting Member

4 Posts

Posted - 2005-12-28 : 17:25:18

Imagine you have a table with 2 columns:

ProductID, ModifyDate

Now imagine that you have data in the table that consists of, primarily, pairs of entries, grouped by ProductID, IE:

ID ModifyDate
-- ----------
1 1/1/2005
1 1/2/2005
2 1/1/2005
2 1/4/2005
...

etc. (ProductID is not a PK on this table, dupes are allowed and expected)

I want to query that table and get the row, one for each pair, with the lowest ModifyDate. In other words, my final result shold look like:

ID ModifyDate
-- ----------
1 1/1/2005
2 1/1/2005
...

Is this a problem that requires some combination of Min() and grouping? Also, the potential exists for there to be only one row per ID or x rows per ID.

This is either very simple and I can't see the forest for the trees or...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-12-28 : 17:51:42

SELECT ID, MIN(ModifyDate) AS ModifyDate
FROM Table1
GROUP BY ID

Tara Kizer
aka tduggan