sql - SQLite query to select matching rows and 2 next with highest IDs

Question

My table has 4 columns:

This is my database.

ID      SSID      BSSID         RSSI
1.      jbhd      ed:2d:5c      -60 
2.      ABCD      ab:cd:17      -68
3.      ijkl      cs:gb:d6      -75 
4.      vxfs      dc:5g:f4      -72
5.      cxzv      fg:4d:ac      -54
6.      ABCD      ab:cd:17      -68
7.      ertd      bv:we:12      -57
8.      erbc      gd:56:lt      -83

....
518.    ABCD      ab:cd:17      -68
519.    asfd      ag:4g:32      -60
520.    aasd      gd:5g:56      -79

I'm trying to write a function which query the database to return a specific record and 2 next records with highest ID.

What I've done so far is:

public Cursor get3records(String mac, int level){
    SQLiteDatabase db = this.getReadableDatabase();
    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)});
    return res;
}

mac and level are parameters that decide which records I want to get from the query.

For instance, take a look at the sample data provided. I want to get all records that have ab:cd:17 in BSSID column and -68 in RSSI column (records 2, 6, 518), and 2 next records with the highest ID (records 3, 4, 7, 8, 519, 520).

The problem is, this function returns result like this:

ID      SSID      BSSID         RSSI
2.      ABCD      ab:cd:17      -68
3.      ijkl      cs:gb:d6      -75 
4.      vxfs      dc:5g:f4      -72

Whereas the result I'm looking for should be like this:

ID      SSID      BSSID         RSSI
2.      ABCD      ab:cd:17      -68
3.      ijkl      cs:gb:d6      -75 
4.      vxfs      dc:5g:f4      -72
6.      ABCD      ab:cd:17      -68
7.      ertd      bv:we:12      -57
8.      erbc      gd:56:lt      -83
518.    ABCD      ab:cd:17      -68
519.    asfd      ag:4g:32      -60
520.    aasd      gd:5g:56      -79

标签： sqlsqliteandroid-sqlite

Answer 1

select * from tablename where id in(
select id from tablename where BSSID=? and RSSI=?,
(select id from tablename where BSSID=? and RSSI=?)+1,
(select id from tablename where BSSID=? and RSSI=?)+2)