CODEWITHSUNDEEP
androidsqlsqlite
masoholic
masoholic

Reputation: 15

SQLite query to select one row and return it with 2 next rows

my database looks like this

ID      SSID      BSSID         RSSI
1.      ABCD      ab:cd:17      -68 
2.      efgh      cd:as:25      -60
3.      ijkl      cs:gb:d6      -75 
4.      abcd      dc:5g:f4      -72 
....
518.    ABCD      ab:cd:17      -68
519.    asfd      ag:4g:32      -60
520.    aasd      gd:5g:56      -79

I'm trying to create a function which selects row based on the input parameters of the function and return this row with 2 next rows.

What I've done so far is that the function returns the record with matching parameters.

public Cursor getRequiredData(String mac, int level){
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor res = db.rawQuery("SELECT BSSID, RSSI FROM Scans_table WHERE BSSID =? AND RSSI =?", new String[] {mac, String.valueOf(level)});
    return res;
}

The result I'm looking for is, for example if macparameter is ab:cd:17 and level = -68 the function returns rows 1, 2, 3 (sample data provided)

EDIT:

Cursor res = db.rawQuery("SELECT * from Scans_table st where ID >= " +
            "( select ID from Scans_table where BSSID =? AND RSSI =? ) order by st.ID asc limit 3 ", new String[] {mac, String.valueOf(level)});

This query works fine, but it just gives me the first record that matches mac and String.valueOf(level) parameters, and next 2 records with the highest ID. What if my table has more than one record with same values of these parameters? Is there a way to get all matching records and 2 nexts with highest ID?(sample data)

Upvotes: 1

Views: 1262

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Assuming by "next rows" you mean "rows with the next highest ids", you can use a subquery to get the id for the row you want and then order by and limit to get the next two rows:

select st.*
from scans_table st
where st.id >= (select st2.id from scans_table st2 where <your conditions are here>)
order by st.id asc
limit 3;

Upvotes: 3

Related Questions