CODEWITHSUNDEEP
sqlite
user3838440
user3838440

Reputation: 15

Getting the nth highest value row in sqlite

My problem sounds simple enough but I havent been able to find a solution that works. I need to get the row with say 5th highest value of an attribute with sqlite.. Entries are random of course. So its not sorted. I did find a few solutions with sql but apparently not all sql functionalities are supported by sqlite.

Thanks in advance

Upvotes: 1

Views: 1303

Answers (2)

Dave D
Dave D

Reputation: 184

Try the 'Limit' keyword.

The query below will eliminate the first four rows by using 'not in'. Then the last 'Limit 1' will select the 5th row.

Select * from Programs Where ProgramId not in (Select ProgramId From Programs order by programId limit 4 ) order by programId Limit 1

EDIT:

To add in CV's 'Distinct' and 'OFFSET' suggestions, the finished query would look something like...

Select StudentName From Students Where Marks in (
Select distinct Marks from Students Order By Marks desc Limit 1 offset 4)

Upvotes: 0

CL.
CL.

Reputation: 180010

To remove duplicates, use DISTINCT. To get only the fifth value, use the OFFSET clause:

SELECT DISTINCT SomeAttribute
FROM MyTable
ORDER BY 1 DESC
LIMIT 1 OFFSET 4

Upvotes: 2

Related Questions