Reputation: 63
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
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
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
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
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
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
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