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