Alexandru Lucian Susma Alexandru Lucian Susma - 2 months ago 6
MySQL Question

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

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

Answer

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.

Comments