CODEWITHSUNDEEP
sqlamazon-redshift
kee
kee

Reputation: 11629

SQL: Group rows by field a or field b

I am not sure this is possible in SQL. I have two fields "a" and "b" in my table. I want to group records in the table when rows have the same "a" values or the same "b" values and then extract a value of another field with the latest timestamp. Would this be possible? I am using Redshift.

Here is an example table:

|| key1 || key2 || date || value ||
| 1 | 'a' | '2019-01-01' | 'john' |
| 1 | 'b' | '2019-01-03' | 'ben' |
| 2 | 'c' | '2019-02-01' | 'kim' |
| 3 | 'c' | '2019-02-02' | 'dan' |
| 4 | 'd' | '2019-02-03' | 'jan' |

Eventually what I want to have is:

| 'ben' |
| 'dan' | 
| 'jan' |

Upvotes: 0

Views: 70

Answers (2)

D-Shih
D-Shih

Reputation: 46219

You can try to write a result set by ROW_NUMBER and SUM window function for those two keys.

Then get rn = 1 latest row from the group.

Query 1:

SELECT value 
FROM (
  SELECT value,
         sum(1) OVER(PARTITION BY key1 ORDER BY key1) cnt1,
         sum(1) OVER(PARTITION BY key2 ORDER BY key2) cnt2 ,
         ROW_NUMBER () OVER(PARTITION BY key1 ORDER BY date DESC) rn1,
         ROW_NUMBER () OVER(PARTITION BY key2 ORDER BY date DESC) rn2
  FROM T
) t1
WHERE 
   (rn1 = 1 and cnt1 > 1) 
or
   (rn2 = 1 and cnt2 > 1)
or 
   (cnt1 = 1 and cnt2 = 1)

Results:

| value |
|-------|
|   ben |
|   dan |
|   jan |

Upvotes: 1

Rich G
Rich G

Reputation: 256

Assumption: date values are unique for all records.

I came up with this:

select mytable.value1
  from (select key1, max(date1) max_date1 from mytable group by key1) q1
  inner join (select key2, max(date1) max_date1 from mytable group by key2) q2
    on q2.max_date1 = q1.max_date1
  inner join mytable on mytable.date1 = q1.max_date1

Based on this schema:

create table myTable (
  key1 int,
  key2 nvarchar(1),
  date1 date,
  value1 nvarchar(20)
  );

insert into mytable values
  (1, 'a', '2019-01-01', 'john'),
  (1, 'b', '2019-01-03', 'ben'),
  (2, 'c', '2019-02-01', 'kim'),
  (3, 'c', '2019-02-02', 'dan');

Upvotes: 0

Related Questions