CODEWITHSUNDEEP
sqldatabasems-access-2010
Mattia Nocerino
Mattia Nocerino

Reputation: 1513

Select only one row from a group by?

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?

Upvotes: 0

Views: 1459

Answers (4)

GarethD
GarethD

Reputation: 69769

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)
                )
        );

Upvotes: 2

onroadrui
onroadrui

Reputation: 1

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.

Upvotes: 0

AxelWass
AxelWass

Reputation: 1341

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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
              );

Upvotes: 0

Related Questions