Hello,
This is a bit long, but I am having some difficulties with selecting records in the following manner:
The records are to be ordered based on 4 parameters, where -
1st consideration is: Prioritized threads (T DESC)
2nd consideration is: Order of threads based on dates of posts included within them (Date DESC). One new post is sufficient to push the whole thread upwards. All posts included in a thread share the same N. BUT:
3rd consideration is: Within the thread, posts should be ordered the other way around (old to new – Date ASC) and if prioritized within thread (P DESC), must appear at the beginning of it.
Here is an example:
N_ __Date___ T P Text
45 01-04-2000 5 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
45 02-04-2000 5 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-06-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-07-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-08-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-06-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 04-05-2000 3 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 02-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 03-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 05-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
50 07-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
50 09-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 08-03-2000 3 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 05-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 06-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
* The 1st thread (45) is located at the top regardless of the dates of its posts, having a high priority of 5. Within it, the posts are ordered old to new.
* The 2nd thread (23) is the newer of all (its last post is the last to be published among all) so it’s located after the prioritized thread, having its posts ordered normally (old to new).
* The 3rd (39) thread is the next in line based on the same reason.
* The 4th (17) thread is next, but within it there is a prioritized post which although not being the thread’s oldest, appears at the top of it. The rest of posts are ordered normally.
* The 5th (50) thread is the next.
* The 6th (61) also includes a prioritized post.
I tried to write an SQL line which would make all of this, and got very very close, but there’s always something missing and when resolving it, another thing which worked before doesn’t anymore. I can’t seem to get all of it together. Any suggestions?
This is a bit long, but I am having some difficulties with selecting records in the following manner:
The records are to be ordered based on 4 parameters, where -
1st consideration is: Prioritized threads (T DESC)
2nd consideration is: Order of threads based on dates of posts included within them (Date DESC). One new post is sufficient to push the whole thread upwards. All posts included in a thread share the same N. BUT:
3rd consideration is: Within the thread, posts should be ordered the other way around (old to new – Date ASC) and if prioritized within thread (P DESC), must appear at the beginning of it.
Here is an example:
N_ __Date___ T P Text
45 01-04-2000 5 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
45 02-04-2000 5 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-06-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-07-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
23 01-08-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
39 01-06-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 04-05-2000 3 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 02-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 03-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
17 05-05-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
50 07-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
50 09-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 08-03-2000 3 1 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 05-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
61 06-03-2000 3 0 xxxxxxxxxxxxxxxxxxxxxxxxxxxx
* The 1st thread (45) is located at the top regardless of the dates of its posts, having a high priority of 5. Within it, the posts are ordered old to new.
* The 2nd thread (23) is the newer of all (its last post is the last to be published among all) so it’s located after the prioritized thread, having its posts ordered normally (old to new).
* The 3rd (39) thread is the next in line based on the same reason.
* The 4th (17) thread is next, but within it there is a prioritized post which although not being the thread’s oldest, appears at the top of it. The rest of posts are ordered normally.
* The 5th (50) thread is the next.
* The 6th (61) also includes a prioritized post.
I tried to write an SQL line which would make all of this, and got very very close, but there’s always something missing and when resolving it, another thing which worked before doesn’t anymore. I can’t seem to get all of it together. Any suggestions?
