0

I have a table

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+

I need to get 2 paintings for each painter with the highest rate.

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+

And delete everything else!

  • Assume "Bath" have rating 5, not 4. So "Nude" is 8, "Bath" and "Window" are 5 each... what record must be deleted - "Bath" or "Window"? and why? – Akina Jul 4 '19 at 12:42
1
WITH 
cte AS ( SELECT author, 
                name, 
                rating, 
                ROW_NUMBER() OVER ( PARTITION BY author 
                                    ORDER BY rating DESC ) rn
         FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
               FROM cte
               WHERE ratings.author = cte.author
                 AND ratings.name = cte.name
                 AND cte.rn > 2 );

fiddle

1

Another version using row_number() window function (And thus needing sqlite 3.25 or newer):

DELETE FROM ratings
WHERE rowid IN (SELECT rowid
                FROM (SELECT rowid
                           , row_number() OVER (PARTITION BY painter ORDER BY rate DESC) AS rn
                      FROM ratings)
                WHERE rn > 2);
0

Using python script.

connection = sqlite3.connect("YOUR_DB.db")
cursor = connection.cursor()

# Select painters with number of its occurance
sql = "SELECT DISTINCT Painter, COUNT(*) FROM Paintings GROUP BY PainterORDER BY COUNT(*) DESC"

# Get only artists with occurance greater than N
data = [artist[0] for artist in selected_data if artist[1] > 150]

while data:

    # One by one get artists and select N-th max rate
    artist = data.pop(0)
    sql = "SELECT Champ51 FROM Paintings WHERE Champ1 = ? ORDER BY Champ51 DESC LIMIT 1 OFFSET 149"
    selected_data = cursor.execute(sql, [artist]).fetchall()
    last = selected_data[0][0]

    # Delete rate less than N-th
    sql = "DELETE FROM Paintings WHERE Champ1 = ? AND Champ51 < ?"
    cursor.execute(sql, [artist, last])
    connection.commit()
  • Data operations must be performed on the database server side using proper query. – Akina Jul 4 '19 at 12:47
  • 1
    Where do Champ1, Champ51, and this 150 magic number come from? And why the DISTINCT? – Shawn Jul 5 '19 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.