SQL grouped columns

Question

I have selection from table (id, group, type, created). How do I select the rows only before first encounter of type 'ad' (including 'ad' row). In this example I need rows with ids 8207 - 8214.

  id   | group | type | created_at |
+------+-------+------+------------+
| 8214 |    83 | msg  | 1571726466 |
| 8213 |    83 | msg  | 1571724983 |
| 8212 |    83 | msg  | 1571724982 |
| 8211 |    83 | msg  | 1571724978 |
| 8210 |    83 | msg  | 1570861659 |
| 8209 |    83 | msg  | 1570861656 |
| 8208 |    83 | msg  | 1570861650 |
| 8207 |    83 | ad   | 1570861643 |
| 8206 |    83 | msg  | 1570861632 |
| 8205 |    83 | msg  | 1570861623 |
| 8202 |    83 | msg  | 1570861617 |
| 8203 |    83 | msg  | 1570861617 |
| 8204 |    83 | msg  | 1570861617 |
| 8200 |    83 | msg  | 1570861616 |
| 8201 |    83 | msg  | 1570861616 |
| 8197 |    83 | msg  | 1570861615 |
| 8198 |    83 | msg  | 1570861615 |
| 8199 |    83 | msg  | 1570861615 |
| 8194 |    83 | msg  | 1570861614 |
| 8195 |    83 | ad   | 1570861614 |
| 8196 |    83 | msg  | 1570861614 |
| 8192 |    83 | msg  | 1570861613 |

Expected:

+------+-------+------+------------+
| id   | group | type | created_at |
+------+-------+------+------------+
| 8214 |    83 | msg  | 1571726466 |
| 8213 |    83 | msg  | 1571724983 |
| 8212 |    83 | msg  | 1571724982 |
| 8211 |    83 | msg  | 1571724978 |
| 8210 |    83 | msg  | 1570861659 |
| 8209 |    83 | msg  | 1570861656 |
| 8208 |    83 | msg  | 1570861650 |
| 8207 |    83 | ad   | 1570861643 |

UPD: Basically I need slice of my selected rows with one row-ad on it.

Have a sub-query that returns the MIN(created_at) value where type = ad! — jarlh, Oct 22 '19 at 07:55
@jarlh. Nice thought. Just a remark since createt_at is on descintnig order, we need MAX rather than MIN — apomene, Oct 22 '19 at 07:58
@jarlh but it will just return one row with min created_at and no ad type. I don't see how it fits for me. — barmaxon, Oct 22 '19 at 07:59
@barmaxon CTE with test data is even better than formatted text, see for example https://stackoverflow.com/a/58492729/653539 — Tomáš Záluský, Oct 22 '19 at 08:04

score 1 · Accepted Answer · answered Oct 22 '19 at 08:02

1

I guess:

select *
from t
where created_at >= (select max(created_at) from t where type = 'ad')

However you don't specify what only before first encounter of type 'ad' means - is table sorted by created_at? Or id? Can duplicate created_at values occur?

answered Oct 22 '19 at 08:02

Tomáš Záluský

10,735
2
36
64

Thx, though it doesn't solve my whole problem, but it pointed to right direction, I guess)). – barmaxon Oct 22 '19 at 08:13

score 1 · Answer 2 · answered Oct 22 '19 at 08:02

1

SELECT * FROM Tbl WHERE Id >= (SELECT MAX(id) FROM Tbl WHERE type = 'ad' GROUP BY ID)

answered Oct 22 '19 at 08:02

Vignesh Kumar A

27,863
13
63
115

score 0 · Answer 3 · answered Oct 22 '19 at 08:03

0

Using sub-query

SELECT * FROM table_name where id >= (SELECT MAX(id) FROM table_name where type = 'ad')

NOTE - you can change order by based on your conditions.

answered Oct 22 '19 at 08:03

Meet Patel

482
4
12

SQL grouped columns

3 Answers3