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


SELECT c1.* 
FROM LiveArt c1
WHERE EXISTS 
      ( SELECT *
        FROM LiveArt c2
        WHERE c1.ArtID < c2.ArtID 
          AND c1.Title = c2.Title 
          AND c1.ArtistID = c2.ArtistID
      )

      



0


source


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







All Articles