CODEWITHSUNDEEP
sqlite
Mark
Mark

Reputation: 205

How to add another column to SELECT DISTINCT query for other columns?

I have this table

CREATE TABLE APmeasure 
(id_APmeasure INTEGER PRIMARY KEY AUTOINCREMENT 
 , RSSI TEXT, TimeOfMeasure DATETIME
 , BSSID TEXT, id_APm INTEGER NOT NULL 
 , FOREIGN KEY (id_APm) REFERENCES APTable (id_Ap) ON DELETE CASCADE)

I want to make a query which would give me distinct results of TimeOfMeasure and BSSID, like this:

SELECT DISTINCT TimeOfMeasure, BSSID 
FROM APmeasure 
WHERE "condition"

But that would retrieve me the other columns on the table, related to the DISTINCT query.

How do I do it?

Upvotes: 0

Views: 776

Answers (1)

mechanical_meat
mechanical_meat

Reputation: 169264

  1. Perform distinct/grouping operation,
  2. Join to result of distinct/grouping operation...

Something like:

SELECT [whichever columns you want]
  FROM APmeasure 
       JOIN (
           SELECT TimeOfMeasure, BSSID 
             FROM APmeasure 
            WHERE [condition]
         GROUP BY TimeOfMeasure, BSSID
       ) x
       ON x.TimeOfMeasure = APmeasure.TimeOfMeasure
   AND x.BSSID = APmeasure.BSSID
       [any other joins you need]

Upvotes: 1

Related Questions