sql combine records based on a column

Question

I have a table which holds 5 columns. I than wrote a basic sql statment which group all columns (to make sure there are no duplicates records).

select flag, year, month, name, max(description) description
from table1
group by flag, year, month, name 
order by year

Sql output result:

flag	year	month	name	description
No	2017	2	name1	random description11
Yes	2017	2	name1	random description112
No	2018	7	name4	random description44
Yes	2022	4	name8	random description999
No	2022	4	name8	random description999

Issue: I am getting 5 reocrds, however i want them to combine into 3 records.

1st & 2nd records are the same bc they have the same year, month, & name; therefor it should be combined into one records and show row with flag column value is yes.

3rd record is has only one record so just show that.

4th & 5th records are the same bc they have the same year, month, & name; therefor it should be combined into one records and show row with flag column value is yes.

What i need:

flag	year	month	name	description
Yes	2017	2	name1	random description112
No	2018	7	name4	random description44
Yes	2022	4	name8	random description999

Does this answer your question? [Select First Row of Every Group in sql](https://stackoverflow.com/questions/16529701/select-first-row-of-every-group-in-sql) — astentx, Jun 12 '23 at 20:07
@astentx stop flagging this post as duplicate... solution `ROW_NUMBER()` didnt worked for me. please re-read my post, maybe that will help — user1924249, Jun 13 '23 at 13:55
"didn't work" provides no information about an actual issue. It actually does https://dbfiddle.uk/SYld_5zZ — astentx, Jun 13 '23 at 14:03

score 1 · Accepted Answer · answered Jun 12 '23 at 22:49

You can use MAX with KEEP:

SELECT MAX(flag) AS flag,
       year,
       month,
       name,
       MAX(description) KEEP (DENSE_RANK LAST ORDER BY flag) AS description
FROM   table1
GROUP BY year, month, name 
ORDER BY year;

or the ROW_NUMBER analytic function:

SELECT flag,
       year,
       month,
       name,
       description
FROM   (
  SELECT flag,
         year,
         month,
         name,
         description,
         ROW_NUMBER() OVER (
           PARTITION BY year, month, name ORDER BY flag DESC, description DESC
         ) AS rn
  FROM   table1
)
WHERE  rn = 1
ORDER BY year;

thanks, `MAX(flag)` solution worked for me – user1924249 Jun 13 '23 at 13:10 — user1924249, Jun 13 '23 at 13:10

score 0 · Answer 2 · answered Jun 12 '23 at 18:36

you can use RWO_NUMBR to filter the wanted rows

WITH CTE1 as (
select flag, year, month, name, max(description) description
from table1
group by flag, year, month, name 
order by year), 
CTE2 AS (SELECT "flag", "year", "month", "name", "description"
  , ROW_NUMBER() OVER(PARTITION BY "year" ORDER BY "flag" DESC) rn
  FROM CTE1)
  SELECT "flag", "year", "month", "name", "description" FROM CTE2
  WHERE rn = 1

score 0 · Answer 3 · answered Jun 12 '23 at 19:22

0

WITH cte AS (
  SELECT flag, year, month, name, MAX(description) AS description
  FROM table1
  GROUP BY flag, year, month, name
)
SELECT flag, year, month, name, description
FROM cte
ORDER BY year;

answered Jun 12 '23 at 19:22

tanay urucu

26
2

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, **can you [edit] your answer to include an explanation of what you're doing** and why you believe it is the best approach? – Jeremy Caney Jun 15 '23 at 00:43

sql combine records based on a column

3 Answers3