CODEWITHSUNDEEP
sqlite
LeonJones
LeonJones

Reputation: 29

sqlite view only produces one result when it should produce many

I have a table squitters which is full of aircraft transmissions. My view called active, returns a list of unique identifiers (hex_ident) and puts them in column d_active_hex. From this view I want to join it with my table and return the most recent transmission that contains lat,lon,alt data for each active hex_ident. My active view is working properly, but my second view is only returning one entry when I know there should be multiple, up to and including but not necessarily the number of active hex_idents. Here's the request.

cur.execute("""
            CREATE VIEW IF NOT EXISTS activepos AS
              SELECT hex_ident,max(parsed_time),lat,lon,altitude
              FROM squitters JOIN active
              ON squitters.hex_ident = active.d_hex_ident AND transmission_type = 3;
              

    """)

I'm currently adding the results of my view called active to a list in python and then looping through the list and making a separate request for each list item like this

SELECT hex_ident,max(parsed_time),lat,lon,altitude
                                    FROM squitters
                                    WHERE hex_ident = ? AND transmission_type = 3

which produces the expected result but that seems inefficient and I'd rather simplify my code and learn how to do it in sql at the same time. Messing around with it, it seems like the max(parsed_time) is the issue because it's selecting the latest transmission out of every transmission, not the latest for each hex_ident but not sure how to achieve what I want.

Any ideas?

Upvotes: 1

Views: 16

Answers (1)

forpas
forpas

Reputation: 164089

I suspect that you want to group by hex_ident:

CREATE VIEW IF NOT EXISTS activepos AS
SELECT s.hex_ident, MAX(parsed_time) parsed_time, lat, lon, altitude
FROM squitters s JOIN active a
ON s.hex_ident = a.d_hex_ident 
WHERE transmission_type = 3
GROUP BY s.hex_ident

Also you should qualify all the column names with the table name/alias that contain them. In my code I left unqualified all the columns that I couldn't identify the table's alias.

Upvotes: 1

Related Questions