How to limit results of SQLite per specific group of results?

Question

I have the following problem at work. I have a large table with different columns and few 100 000s of rows. I'll only post the ones im interested in.

Assume the following data set

Device ID, Feature Id, Feature Status

1,        1,        0
1,        2,        0
1,        3,        1
1,        4,        1
1,        5,        1

2,        1,        1
2,        2,        0
2,        3,        0 
2,        4,        1
2,        5,        0

3,        1,        1
3,        2,        1
3,        3,        1
3,        4,        1
3,        5,        1

4,        1,        0
4,        2,        0
4,        3,        1
4,        4,        0
4,        5,        0

I need to select rows with Feature Status = 1 but only the first 2 from each Device Id.

The results of the query should be:

1,3,1
1,4,1
2,1,1
2,4,1
3,1,1
3,2,1
4,3,1

I tried something like this:

SELECT brdsurfid,featureidx,FeatStatus FROM Features F1 WHERE FeatStatus = 1 AND
  (SELECT COUNT(*) FROM Features F2 
  WHERE F2.FeatureIdx <= F1.FeatureIdx AND F2.FeatStatus = 1) < 2
  ORDER BY BrdSurfId,FeatureIdx;

which I found in another response but it didnt quite work.

I know I need to use a mix of LIMIT or COunt(*) and some nested selects but I can't figure it out. Thanks

Because I figured people who know SQL and use MySql could help. Why not? It's a database related question, even it's specific to Sqlite, cross experience might work — Alexandru Lucian Susma, Sep 13 '16 at 19:11

score 1 · Accepted Answer · answered Sep 13 '16 at 19:16

1

This probably not a very efficient way to do this, but I don't think there is a better solution for sqlite (that involves a single query):

SELECT *
FROM t t0
WHERE FeatureStatus AND
      (SELECT count(*)
       FROM t t1
       WHERE t0.DeviceID=t1.DeviceID
         AND FeatureStatus
         AND t1.FeatureId<t0.FeatureId
      )<2;

I assume that the table is called t. The idea is to find all features where the status is 1 and then for each feature to count the previous features with that status for the same product. If that count is more than 2, then reject the row.

answered Sep 13 '16 at 19:16

redneb

21,794
6
42
54

let me try it !. This is what i was trying to do myself. – Alexandru Lucian Susma Sep 13 '16 at 19:24
it doesnt work. It returns all the rows where FeatStatus = 1. :( – Alexandru Lucian Susma Sep 13 '16 at 19:25
@AlexandruLucianSusma Are you sure? I just run it again with your sample data and it only returns features 3 and 4 (not 5) for device id 1. – redneb Sep 13 '16 at 19:28
it works fine you are right ! I misstyped something !! Thank you very much ! – Alexandru Lucian Susma Sep 13 '16 at 19:37

luke · Answer 2 · 2016-09-13T19:51:38.803

Not sure if this will work with sqlite but for what its worth...

 ;with result as
 (
      SELECT
         brdsurfid,
         featureidx,
         FeatStatus ,
         ROW_NUMBER() OVER(PARTITION BY brdsurfid ORDER BY fieldWhatever) AS 'someName1',
         ROW_NUMBER() OVER(PARTITION BY featureidx ORDER BY fieldWhatever) AS 'someName2',
         ROW_NUMBER() OVER(PARTITION BY FeatStatus ORDER BY fieldWhatever) AS 'someName3'

         FROM
            Features
  )
  SELECT * 
  FROM 
     result
  WHERE 
     FeatStatus = 1 AND
     someName1 <= 2 AND
     someName2 <= 2 AND
     someName3 <= 2

How to limit results of SQLite per specific group of results?

2 Answers2