I have a table called "titles" which has the following value:
id title time
1 ABC 1-1-2021
2 EEE 1-1-2021
3 ABC 1-1-2020
4 123 2-1-2020
Now I want to select the record from this table with the giving list, e.g.:
let title_to_search = [ABC, 123]
Order by "time" column in DESC
I want to get the following result after searching table "title":
id title time
1 ABC 1-1-2021
4 123 2-1-2020
I have tried the following state:
select * from titles where title in title_to_search order by time DESC
The result is
id title time
1 ABC 1-1-2021
3 ABC 1-1-2020
4 123 2-1-2020
It is caused by id "3" also has title value "ABC". However, what I want is to iterate through the list, which means if "ABC" is already used in where clause, it should not be used again, therefore, record "1" should be selected, and "3" should not be selected, and 4 should be selected.
How can I write such a SQL statement?
I am using MySQL database.
CodePudding user response：
Following will work on sample data, with all extra data:
Select * from titles where id in (SELECT MIN(ID) FROM TITLES WHERE TITLE IN TITLE_TO_SEARCH GROUP BY TITLE)
CodePudding user response：
I think you need something like this
SELECT a.*
FROM titles a
WHERE a.title IN ( 'ABC', '123' )
AND NOT EXISTS (
SELECT 1
FROM titles b
WHERE b.title = a.title
AND b.id < a.id
)
b.id < a.id could change according to your requirement, probably by time?
About Performance
mysql> SELECT COUNT(*) FROM titles a WHERE a.title IN ( 'A2617', 'A6983' );
----------
| COUNT(*) |
----------
| 33 |
----------
1 row in set (0.00 sec)
mysql> select count(*) from titles;
----------
| count(*) |
----------
| 707280 |
----------
1 row in set (0.03 sec)
mysql> EXPLAIN SELECT a.* FROM titles a WHERE a.title IN ( 'A2617', 'A6983' ) AND NOT EXISTS ( SELECT 1 FROM titles b WHERE b.title = a.title AND b.id < a.id );
---- ------------- ------- ------------ ------- --------------- ------- --------- -------------- ------ ---------- --------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---- ------------- ------- ------------ ------- --------------- ------- --------- -------------- ------ ---------- --------------------------------------
| 1 | SIMPLE | a | NULL | range | _Idx1 | _Idx1 | 32 | NULL | 33 | 100.00 | Using index condition |
| 1 | SIMPLE | b | NULL | ref | PRIMARY,_Idx1 | _Idx1 | 32 | test.a.title | 1 | 100.00 | Using where; Not exists; Using index |
---- ------------- ------- ------------ ------- --------------- ------- --------- -------------- ------ ---------- --------------------------------------
2 rows in set, 3 warnings (0.00 sec)
mysql> SELECT a.* FROM titles a WHERE a.title IN ( 'A2617', 'A6983' ) AND NOT EXISTS ( SELECT 1 FROM titles b WHERE b.title = a.title AND b.id < a.id );
-------- ------- ------------
| id | title | time |
-------- ------- ------------
| 404071 | A2617 | 2021-12-14 |
| 397458 | A6983 | 2021-12-14 |
-------- ------- ------------
2 rows in set (0.01 sec)
mysql> SELECT a.* FROM titles a WHERE a.title IN ( 'A2617', 'A6983' );
-------- ------- ------------
| id | title | time |
-------- ------- ------------
| 404071 | A2617 | 2021-12-14 |
| 495677 | A2617 | 2021-12-14 |
| 505974 | A2617 | 2021-12-14 |
| 522148 | A2617 | 2021-12-14 |
| 523088 | A2617 | 2021-12-14 |
| 545776 | A2617 | 2021-12-14 |
| 546174 | A2617 | 2021-12-14 |
| 565224 | A2617 | 2021-12-14 |
| 594082 | A2617 | 2021-12-14 |
| 634224 | A2617 | 2021-12-14 |
| 663641 | A2617 | 2021-12-14 |
| 684980 | A2617 | 2021-12-14 |
| 727136 | A2617 | 2021-12-14 |
| 733046 | A2617 | 2021-12-14 |
| 738378 | A2617 | 2021-12-14 |
| 747396 | A2617 | 2021-12-14 |
| 397458 | A6983 | 2021-12-14 |
| 398483 | A6983 | 2021-12-14 |
| 416826 | A6983 | 2021-12-14 |
| 418081 | A6983 | 2021-12-14 |
| 426902 | A6983 | 2021-12-14 |
| 454826 | A6983 | 2021-12-14 |
| 479057 | A6983 | 2021-12-14 |
| 486494 | A6983 | 2021-12-14 |
| 492336 | A6983 | 2021-12-14 |
| 515798 | A6983 | 2021-12-14 |
| 570730 | A6983 | 2021-12-14 |
| 587073 | A6983 | 2021-12-14 |
| 627372 | A6983 | 2021-12-14 |
| 672636 | A6983 | 2021-12-14 |
| 699460 | A6983 | 2021-12-14 |
| 722743 | A6983 | 2021-12-14 |
| 747387 | A6983 | 2021-12-14 |
-------- ------- ------------
33 rows in set (0.00 sec)