Using GROUP BY, select ID of record in each group that has lowest ID

Question

I am creating a file orginization system where you can add content items to multiple folders. I am storing the data in a table that has a structure similar to the following:

ID       TypeID    ContentID    FolderID
1         101        1001          1
2         101        1001          2
3         102        1002          3
4         103        1002          2
5         103        1002          1
6         104        1001          1
7         105        1005          2

I am trying to select the first record for each unique TypeID and ContentID pair. For the above table, I would want the results to be:

ID
1
3
4
6
7

As you can see, the pairs 101 1001 and 103 1002 were each added to two folders, yet I only want the record with the first folder they were added to.

When I try the following query, however, I only get result that have at least two entries with the same TypeID and ContentID:

select MIN(ID)
from table
group by TypeID, ContentID

results in

ID
1
4

If I change MIN(ID) to MAX(ID) I get the correct amount of results, yet I get the record with the last folder they were added to and not the first folder:

ID
2
3
5
6
7

Am I using GROUP BY or the MIN wrong? Is there another way that I can accomplish this task of selecting the first record of each TypeID ContentID pair?

Answer 1

Use it with ORDER BY:

select *
from table
group by TypeID, ContentID
order by id

SQLFiddle: http://sqlfiddle.com/#!9/024016/12

Answer 2

Use ROW_NUMBER

WITH CTE AS
(SELECT ID,TypeID,ContentID,FolderID,
ROW_NUMBER() OVER (PARTITION BY TypeID,ContentID ORDER BY ID) as rn FROM t
)
SELECT ID FROM CTE WHERE rn=1

Answer 3

MIN() and MAX() should return the same amount of rows. Changing the function should not change the number of rows returned in the query.

Is this query part of a larger query? From looking at the sample data provided, I would assume that this code is only a snippet from a larger action you are trying to do. Do you later try to join TypeID, ContentID or FolderID with the tables the IDs are referencing?
If yes, this error is likely being caused by another part of your query and not this select statement. If you are using joins or multi-level select statements, you can get different amount of results if the reference tables do not contain a record for all the foreign IDs.

Another suggestion, check to see if any of the values in your records are NULL. Although this should not affect the GROUP BY, I have sometime encountered strange behavior when dealing with NULL values.

Answer 4

Try with first ( id) instead of min(id)

select first(id)
from table 
group by TypeID, ContentID

It works ?