Select only one row from a group by?

Question

I am using Microsoft Access 2010 and I have a table T_Offers that looks like this:

Key    ID    Date          Name           Text
---    --    ----------    -----------    -----------  
1      10    10/10/2015    Lorem          Consectetur
2      10    10/10/2015    Ipsum          Amet
3      11    27/09/2014    Dolor          Sit
4      13    12/11/2013    Sit            Dolor
5      14    11/07/2015    Amet           Ipsum
6      14    12/07/2015    Consectetur    Lorem

I need to get only one row of each ID (the one with the smallest date), so, for example, the result of this table would be:

Key    ID    Date          Name           Text
---    --    ----------    -----------    -----------  
1      10    10/10/2015    Lorem          Consectetur
3      11    27/09/2014    Dolor          Sit
4      13    12/11/2013    Sit            Dolor
5      14    11/07/2015    Amet           Ipsum

This is one of the queries i've tried:

SELECT ID, name, text, MIN (date) AS minDate
FROM (SELECT ID, name, text, date
      FROM T_Offers
      GROUP BY ID, name, text, date
      ORDER BY ID asc) as X
GROUP BY ID, name, text

This would work fine, but there's a little problem: if 2 offers with the same ID have the same date, the result table would duplicate ID, and i don't want that to happen. Is there an alternative to this problem?

GarethD · Accepted Answer · 2015-09-04T08:39:48.797

2

You can use NOT EXISTS to exclude all rows where another row with the same ID and an earlier date exists:

SELECT  t1.Key, t1.ID, t1.Date, t1.Name, t1.Text
FROM    t_offers AS t1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    T_Offers AS t2
            WHERE   t2.ID = t1.ID
            AND     t2.Date < t1.Date
        );

This will leave 1 row per ID, and it will be the row with the earliest date.

With regard to then removing duplicates where the first date is the same, I am not sure of your logic, but you may need to build in further checks which could get quite messy. In this case I have used Key to determine which of the two records should be returned.

SELECT  t1.Key, t1.ID, t1.Date, t1.Name, t1.Text
FROM    t_offers AS t1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    T_Offers AS t2
            WHERE   t2.ID = t2.ID
            AND (   t2.Date < t1.Date
                OR  (t2.Date = t1.Date AND t2.Key < t1.Key)
                )
        );

edited Sep 04 '15 at 08:39

answered Sep 03 '15 at 16:58

GarethD

68,045
10
83
123

1

This is the answer, thanks (anyway there's a little error i think in the subquery condition t2.ID = t2.ID should be t2.ID = t1.ID right? And another question: could you explain what "SELECT 1" mean? – Mattia Nocerino Sep 04 '15 at 08:37
1

Yes indeed it should be `t2.ID = t1.ID`. I have corrected this. Inside an `EXISTS` clause the columns are not retrieved, but you are still required to put something after the `SELECT`. For me `SELECT 1` is simply a stylistic choice, others prefer `SELECT NULL` or `SELECT *`. I choose `SELECT 1` because it is shorter, and shows the intent that I do not care about values, simply that a row exists. There's a [related question here](http://stackoverflow.com/q/4876166/1048425) – GarethD Sep 04 '15 at 08:48

score 0 · Answer 2 · answered Sep 03 '15 at 16:31

0

Assuming that the ids are not duplicated, here is one way to do this in Access:

select o.*
from t_offers as o
where o.key = (select min(o2.key)
               from t_offers as o2
               where o2.id = o.id
              );

answered Sep 03 '15 at 16:31

Gordon Linoff

1,242,037
58
646
786

Sorry if i'm asking just now, I forgot to include this in the question: is there a way to select only 1 record per id but the one with the smallest date? – Mattia Nocerino Sep 03 '15 at 16:40
@MattiaNocerino . . . According to the data in the question, it looks like `key` is unique, so this *will* select only one record per id. – Gordon Linoff Sep 03 '15 at 23:37

score 0 · Answer 3 · answered Sep 03 '15 at 16:39

0

You need a select distinct query:

SELECT DISTINCT ID, name, text, MIN (date) AS minDate
FROM T_Offers
GROUP BY ID, name, text
ORDER BY ID asc;

answered Sep 03 '15 at 16:39

AxelWass

1,321
12
21

This is very similiar to something that i have already tried, and it's giving the same issue: if there are 2 offers with the same id AND the same date, i won't get Distinct ids – Mattia Nocerino Sep 03 '15 at 16:42
if they have the same id, name, text and date, distinct will delete the repeated row. But if you have same id, same date, but different name or text it wont. How do you want the query to respond in this case? If you don't care about all distinct names and texts to appear, just don't group by them: `SELECT DISTINCT ID, name, text, MIN (date) AS minDate FROM T_Offers GROUP BY ID ORDER BY ID asc;` – AxelWass Sep 03 '15 at 16:59

score 0 · Answer 4 · answered Sep 03 '15 at 17:15

this is my solution.Althouth my test environment is mysql, the sql grammar is the same.

SELECT TO1.KEY,TO1.ID,TO.DATE,TO1.NAME,TO1.TEXT
FROM T_Offer TO1 
INNER JOIN   
(
 select MIN(TO2.KEY) AS KEY  from T_Offer TO2 group by ID 
)TO3 
ON TO1.KEY = TO3.KEY

advice:I help that you can provide your script of create the table and insert the test data if it's easy for you.

Select only one row from a group by?

4 Answers4