SQLite Database selecting MAX(column) on two columns, while also selecting a Distinct value from a c-CodePudding

I'm attempting to Select information from an SQLite Database I created (something similar to this):

  Date      Time    Acc  TotalAcc  Proc  TotalProc
21-12-01 | 00:00 |  133 |     133 |  76 |       76 
21-12-01 | 01:00 |  270 |     403 | 260 |      336 
21-12-01 | 02:00 |   35 |     438 |  24 |      360
21-12-01 | 02:00 |   50 |     453 |  30 |      366 
21-12-02 | 00:00 |  113 |     113 |  89 |       89
21-12-02 | 07:00 |    2 |    1290 |   6 |     1199 
21-12-02 | 07:00 |   28 |    1316 |  17 |     1210 
21-12-02 | 07:00 |  432 |    1720 | 384 |     1577
21-12-02 | 07:00 |  502 |    2222 | 403 |     1975

The information I'm looking to gather: a unique Date (only 1 from each day), with the Max Time (in this case it would be 07:00 for 21-12-01, and 02:00 for 21-12-02).

The final metric I want for sorting (this is where I'm having trouble): I also want to select the row that contains the highest TotalAcc.

Currently, this is the SQL logic I'm using to pull data:

example =

"SELECT DISTINCT Date, TotalAcc, TotalProc, MAX(Time) 
    FROM table_name 
    GROUP BY Date 
    ORDER BY Date DESC, MAX(Time) DESC"

df = pd.read_sql_query(example, con)
print(df)

ouput

The data I'm looking to take from the database should look more like this:

          Date TotalAcc  TotalProc  MAX(Time)
0 | 21-12-02 |      453 |      366 | 02:00
1 | 21-12-01 |     2222 |     1975 | 07:00

I've tried using MAX(TotalAcc) instead of TotalAcc when selecting the data, but it returns me a number that's different from the actual max value in the column for that given time and date. Setting example = 'SELECT MAX(TotalAcc) FROM table_name' returns a non-max value (1290, for example).

I apologize for not giving a total replicable example, I pull my data points from a source, which populates the table I create like this:

with con:
                con.execute('''
                    CREATE TABLE table_name (
                        Date TEXT,
                        Time TEXT,
                        Acc TEXT,
                        TotalAcc TEXT,
                        Proc TEXT,
                        TotalProc TEXT
                    );''')

Any and all ideas are appreciated, SQL logic seems a bit confusing at times.

CodePudding user response：

I suspect that you want a query along these lines:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Date
                                 ORDER BY Time DESC, TotalAcc DESC) rn
    FROM table_name
)

SELECT Date, Time, Acc, TotalAcc, Proc, TotalProc
FROM cte
WHERE rn = 1;

This will return one row per date, having the max time. Should two or more rows on the same date also have the same max time, then the row having the highest TotalProc will be selected.