dimanche 6 décembre 2015

Find a record and its neighbour in an ordered query

I have a list of users. Each user has a score. I'd like to query my database to get a list of 11 users, ordered by score, and centered on a specific record (i'd like the 5 previous records, my queried record, and the 5 following records).

So for example, assuming the following table:

Id  Name      Score 
1   Albert    12
2   Bernard   85
3   Christian 98
4   Danielle  5
5   Emilie    65
6   Fabrice   34
7   Gaston    123
8   Hasting   76
9   Isidor    90
10  Jacques   3
11  Kellam    32
12  Lily      13
13  Mael      4242

If I run my query using Emily as a pivot, I should get the following:

5   Danielle  --- -5
12  Albert    --- -4
13  Lily      --- -3
32  Kellam    --- -2
34  Fabrice   --- -1
65  Emilie    ---  0 (pivot)
76  Hasting   --- +1
85  Bernard   --- +2
90  Isidor    --- +3
98  Christian --- +4
123 Gaston    --- +5

(notice how Jacques and Mael haven't been returned, because they're not in the requested range)

How can I achieve this result by only using a minimal amount of queries and/or only simple queries?

I'm using Postgres, but I'd like something that could also work on SQLite

Aucun commentaire:

Enregistrer un commentaire