CODEWITHSUNDEEP
sql
Alison Aftra
Alison Aftra

Reputation: 63

SQL how can I have sql return one row instead of multiple per distinct ID?

According to my coworker, We are using IBM DB2

Having real hard time trying to make my sql return one row per ID. Asked around at work but no girl here are good with SQL... I can do some sql but am no SQL expert.

To explain it better, I set up the example below

Here is the TABLE and its data

ID  DATE        CODE  PERCENT
01  2016-08-21  1111  52
01  2016-09-06  1111  60
01  2016-10-06  1112  38
02  2016-05-01  6666  50
02  2016-10-01  1111  50

I want one record returned per each ID with earliest DATE. so I wrote below SQL

SELECT ID, MIN(DATE) 
FROM TABLE
WHERE DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
AND   CODE = 1111
GROUP BY ID

that worked fine. I would get 

01 2016-08-21
02 2016-10-01

I soon realized that I also need PERCENT column.

SELECT ID, MIN(DATE), PERCENT
FROM TABLE
WHERE DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
AND   CODE = 1111
GROUP BY ID, PERCENT

but now, I'm getting the multiple rows for ID 01 which is wrong...

01  2016-08-21  1111  52
01  2016-09-06  1111  60
02  2016-10-01  1111  50

Can somebody help me fix this SQL or point me to the right direction so that I can have one row per ID like below?

01  2016-08-21  1111  52
02  2016-10-01  1111  50

Upvotes: 1

Views: 2831

Answers (6)

Zorkolot
Zorkolot

Reputation: 2027

The reason PERCENT defeats the group by is because PERCENT is different between rows and you are removing duplicates with GROUP BY. PERCENT makes the row different... You can instead subquery to re-acquire the row from the table that has the same ID and MIN(DATE)

SELECT dT.ID
      ,dT.DATE
      ,(SELECT T.PERCENT
          FROM TABLE T
         WHERE T.ID = dT.ID AND T.DATE = dT.DATE
       ) AS Percent
  FROM (
         SELECT ID, MIN(DATE) AS DATE
           FROM TABLE
          WHERE DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
            AND   CODE = 1111
         GROUP BY ID
       ) AS dT

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35623

IF you database supports row_number() over() then using that function is an excellent way of determining which rows are "the earliest" like this:

select *
from (
       select *
          , row_number() over(partition by ID, CODE order by DATE ASC) as is_oldest
       from YOURTABLE
       where DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
       and CODE = 1111
     ) d
where is_oldest = 1

The significant advantage of this is it provides access to the whole row associated with "the oldest" without needing a join.

Note the flexibility of this approach too, it can easily be extended e.g.

select *
from (
       select *
          , row_number() over(partition by ID, CODE order by date ASC) as is_oldest
          , row_number() over(partition by ID, CODE order by date DESC) as is_recent
       from your_table
       WHERE DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
       AND   CODE = 1111
     ) d
where is_oldest = 1
or is_recent = 1

All one has to do to get "the most recent" is change the order from ASCending to DESCending.

NB: MySQL prior to v8 does not support "window functions such as row_number, but it is planned at v8 onward. Many other databases do support this function.

Upvotes: 1

Rexx
Rexx

Reputation: 27

Do not group by PERCENT, just group by ID. Add code in the select statement.

select id, min(date), code, percent
from tablename
where date between '2016-01-01' AND  '2017-11-01'
and   code = 1111
group by id;

Upvotes: 0

nish
nish

Reputation: 1221

GROUP BY ID will do the necessary, no need to GROUP BY percent. A new row will be added if you GROUP BY percent since it has a different value.

SELECT ID, MIN(DATE), code, percent
FROM table
WHERE DATE BETWEEN '2016-01-01' AND '2017-11-01'
AND   CODE = 1111
GROUP BY ID;

Hope this helps.

Upvotes: 1

zambonee
zambonee

Reputation: 1647

If this is SQL Server, you can use TOP 1 WITH TIES along with an ORDER BY:

SELECT TOP 1 WITH TIES ID, DATE, PERCENT
FROM TABLE
WHERE DATE >= '2016-01-01' AND  DATE <= '2017-11-01'
AND  CODE = 1111
ORDER BY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY DATE ASC)

Upvotes: 0

Alan
Alan

Reputation: 1428

SELECT
    Earliest.ID,
    Earliest.Date,
    TABLE.PERCENT
FROM (
    SELECT 
        ID, 
        MIN(DATE)
    FROM
        TABLE
    WHERE 
        DATE BETWEEN '2016-01-01' AND  '2017-11-01'
        AND CODE = 1111
    GROUP BY ID
) AS Earliest
INNER JOIN TABLE ON Earliest.ID = Table.ID

To explain why your adjusted query is returning multiple records: The percent column has different data, and so when you group by it, it will add a new record for every change on that.

In this answer you get the percent from the underlying table by joining to the summarised results.

Upvotes: 0

Related Questions