Home > Software engineering >  How to select oldest rows from table where a value type does not exist?
How to select oldest rows from table where a value type does not exist?

Time:12-02

Suppose I have a table like this:

Example data:

x_id | type | user_id | other_id | date
----------------------------------------------
 123 |  AB  |   999   |    001   | 10/12/12
 124 |  CD  |   887   |    258   | 12/12/21
 125 |  CD  |   651   |    702   | 03/04/11
 126 |  AB  |   114   |    003   | 01/19/01
 127 |  EF  |   573   |    777   | 02/08/17
 128 |  AB  |   221   |    145   | 07/21/94
 129 |  CD  |   999   |    001   | 10/12/12
 130 |  CD  |   573   |    777   | 10/10/99
 131 |  EF  |   114   |    003   | 03/02/97
 132 |  EF  |   000   |    914   | 01/01/82

I want to select records with a type of AB, unless that type does not exist for a record. If that's the case, then I would want the oldest record, regardless of the type. For example, this is the result I'm looking for:

Desired result:

x_id | type | user_id | other_id | date
----------------------------------------------
123 |  AB  |   999   |    001   | 10/12/12
124 |  CD  |   887   |    258   | 12/12/21
125 |  CD  |   651   |    702   | 03/04/11
126 |  AB  |   114   |    003   | 01/19/01
128 |  AB  |   221   |    145   | 07/21/94
130 |  CD  |   573   |    777   | 10/10/99
132 |  EF  |   000   |    914   | 01/01/82

I started out by seeing if I could simply return records where the type of AB does not exist via the query below:

SELECT t1.x_id
,t1.type
,MAX(t1.user_id)
,t2.other_id
,MIN(t1.date)
FROM TBL_1 t1
LEFT JOIN TBL_2 t2 ON t1.y_id = t2.y_id
WHERE NOT EXISTS (
SELECT * FROM TBL_1 t1a
WHERE t1.user_id = t1a.user_id
AND t1.type LIKE '           
  • Related