CODEWITHSUNDEEP
sqlsql-order-by
valek
valek

Reputation: 1376

Ordering selected data

I'm making a library project. I want to display persons who have or haven't returned the books in this order: persons who haven't returned books for 15 days already, then persons who haven't returned their books but still have time until 15 days time limit and persons who have returned their books. The table looks like this:

id | pupil  | professor | date_of_taking      | expected_date       | returned | book |
---------------------------------------------------------------------------------------
1  |1       |1          | 2016-01-16 00:00:00 | 2016-01-30 00:00:00 | 0        | 20   |
2  |2       |1          | 2016-01-07 00:00:00 | 2016-01-14 00:00:00 | 0        | 93   |
3  |1       |1          | 2016-01-09 00:00:00 | 2016-01-30 00:00:00 | 1        | 14   |

The pupil column contains the id of the person who took the book. The professor column contains the id of the person who gave the book. The book column contains the id of the taken book. All of this data is in another tables. Date of taking is the date when the person took the book and expected date is the date until when the person is supposed to return it back.

So what I actually want if you didn't understand the previous explanation is this: first show id 2 because the time limit expired and he was supposed to return the book. After that, show 1 because he still hasn't returned the book but has more time left. And finally, show 3 because he has already returned the book. Now just imagine that there are thousands of rows in this table. How to order it to show the data in the order I explained up here.

Here is what I have. I just need help with ordering the data.

SELECT 
    A.date_of_taking,
    A.expected_at,
    A.returned,
    B.name as pupil,
    C.name as prof,
    D.author,
    D.title
FROM taken AS A
INNER JOIN pupils AS B ON A.pupil = B.id
INNER JOIN users AS C ON A.professor = C.id
INNER JOIN books AS D ON A.book = D.id
LIMIT 50

And by the way, the difference between two dates in the table isn't strictly 15 day since this is only a table for testing.
Also this is the code in PHP but I want to merge these 3 queries into one. If it's possible.

$stmt = $this->db()->query("SELECT 
        A.date_of_taking,
        A.expected_at,
        A.returned,
        B.name as pupil,
        C.name as prof,
        D.autor,
        D.title

        FROM taken AS A
        INNER JOIN pupils AS B ON A.pupil = B.id
        INNER JOIN users AS C ON A.professor = C.id
        INNER JOIN books AS D ON A.book = D.id
        WHERE A.returned='0' AND A.date_of_taking < A.expected_at ORDER BY A.date_of_taking ASC");

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
$stmt = $this->db()->query("SELECT 
        A.date_of_taking,
        A.expected_at,
        A.returned,
        B.name as pupil,
        C.name as prof,
        D.autor,
        D.title

        FROM taken AS A
        INNER JOIN pupils AS B ON A.pupil = B.id
        INNER JOIN users AS C ON A.professor = C.id
        INNER JOIN books AS D ON A.book = D.id
        WHERE A.date_of_taking > A.expected_at ORDER BY A.date_of_taking ASC");

$var = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($var as $value) {
    $result[] = $value;
}
$stmt = $this->db()->query("SELECT 
        A.date_of_taking,
        A.expected_at,
        A.returned,
        B.name as pupil,
        C.name as prof,
        D.autor,
        D.title

        FROM taken AS A
        INNER JOIN pupils AS B ON A.pupil = B.id
        INNER JOIN users AS C ON A.professor = C.id
        INNER JOIN books AS D ON A.book = D.id
        WHERE A.returned='1' ORDER BY A.date_of_taking ASC");

$var = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($var as $value) {
    $result[] = $value;
}
return $result;

Upvotes: 0

Views: 34

Answers (2)

Ben Link
Ben Link

Reputation: 107

If you specify

ORDER BY RETURNED, EXPECTED_DATE

Then all returned = 0 come before returned = 1 and within that order expected_date that are more in future will order within that.

Upvotes: 1

mauro
mauro

Reputation: 5940

Did I miss something or what you need is just:

SELECT 
    A.date_of_taking,
    A.expected_at,
    A.returned,
    B.name as pupil,
    C.name as prof,
    D.author,
    D.title
FROM taken AS A
INNER JOIN pupils AS B ON A.pupil = B.id
INNER JOIN users AS C ON A.professor = C.id
INNER JOIN books AS D ON A.book = D.id
ORDER BY ( CURRENT_DATE -  A.date_of_taking ) DESC  
LIMIT 50

Note 1 Different databases might have different date arithmetic rules. So you might need to change ( DATE2 - DATE1 ) with another database-depending function returning the number of days between two dates (for example DATEDIFF())

Note2 Several databases use CURRENT_DATE to return the... current date. In other cases this could be SYSDATE or something else.

Upvotes: 0

Related Questions