CODEWITHSUNDEEP
pythonpython-3.xsqlite
Javierd98
Javierd98

Reputation: 810

Python sqlite3 select without repeating field

i'm working in python with a sqlite3 database, and I need to get all the data from a date, but I just need the last row from each user. Let me explain myself: My current code is this:

conn = sqlite3.connect('database.db')
c = conn.cursor()

t = (user, pastTS)
resultPoints = c.execute("SELECT * FROM table WHERE user != ? AND timestamp >= ?", t)
conn.close()

The problem with this is that it returns me multiple entries / rows from the same user, when I just want the last one. Is there any easy way to achieve that or should I analayze the cursor in order to remove the repetitive fields?

Upvotes: 2

Views: 73

Answers (2)

CL.
CL.

Reputation: 180080

To get one output row for each user, use grouping. To select which row from each group to return, use MAX():

c.execute("""SELECT user,
                    [other fields],
                    MAX(timestamp) AS timestamp
             FROM MyTable
             WHERE user != ?
               AND timestamp >= ?
             GROUP BY user
          """, (user, pastTS))

Upvotes: 1

alecxe
alecxe

Reputation: 473873

If I understand your problem correctly, you can group by the user and get the maximum timestamp value per user, then join with the table and output the results:

c.execute("""
    SELECT *
    FROM (
          SELECT user, MAX(timestamp) as MaxTime
          FROM table
          GROUP BY user
    ) t2
    INNER JOIN table t1
    ON t1.user != ? and t1.user = t2.user AND t1.timestamp = t2.MaxTime
""", t)

result = c.fetchall()
print(result)

Upvotes: 2

Related Questions