How can I skip the matched result of a subquery?
Using sqlite3 I have a table that looks like this:
+---------+-----------------+----------+-----------+--------+
| ArtId | Location | ArtistID | Title | Size |
+---------+-----------------+----------+-----------+--------+
| 3 | China | 400 | birds | small |
| 4 | Samoa | 670 | stars | large |
| 5 | Chile | 427 | clouds | medium |
| 6 | US | 427 | clouds | small |
| 7 | France | 123 | collage | small |
| 8 | Spain | 123 | collage | large |
| 9 | Belarus | 123 | collage | medium |
+---------+-----------------+----------+-----------+--------+
I have a query that gives all the results where the only results are those with duplicate titles and artists:
SELECT *
FROM LiveArt c1, (SELECT Title, ArtistID FROM LiveArt GROUP BY Title, ArtistID) c2
WHERE c1.Title = c2.Title AND c1.ArtistID = c2.ArtistID
to create the following table:
+---------+-----------------+----------+-----------+--------+
| ArtId | Location | ArtistID | Title | Size |
+---------+-----------------+----------+-----------+--------+
| 5 | Chile | 427 | clouds | medium |
| 6 | US | 427 | clouds | small |
| 7 | France | 123 | collage | small |
| 8 | Spain | 123 | collage | large |
| 9 | Belarus | 123 | collage | medium |
+---------+-----------------+----------+-----------+--------+
I want to return this:
+---------+-----------------+----------+-----------+--------+
| ArtId | Location | ArtistID | Title | Size |
+---------+-----------------+----------+-----------+--------+
| 6 | US | 427 | clouds | small |
| 8 | Spain | 123 | collage | large |
| 9 | Belarus | 123 | collage | medium |
+---------+-----------------+----------+-----------+--------+
How can I customize my query to do this (skip the first consistent result)?
+3
source to share
3 answers
select * from tabName A
where A.ArtId !=
(
select min(ArtId)
from tabName B
group by Title
having A.Title=B.Title
);
ArtId Location ArtistID Title Size
---------- ---------- ---------- ---------- ----------
6 US 427 clouds small
8 Spain 123 collage large
9 Belarus 123 collage medium
+1
source to share
There is probably a more efficient way, but something like this might solve the problem for you:
SELECT *
FROM LiveArt c1,
(
SELECT Title, ArtistID
FROM LiveArt
GROUP BY Title, ArtistID
) c2
WHERE c1.Title = c2.Title AND c1.ArtistID = c2.ArtistID
AND c1.ARTID NOT IN
(
SELECT MIN(ArtID)
FROM LiveArt
GROUP BY Title, ArtistID
)
0
source to share