In SQL: Command Unique Items by Related Timestamp Items

I have two tables “loans” and “payments”. Loans have a loan status and may have payments attached to them. Payments have a loan id, a overdue status and a processed date.

I want to get a list of all the loans with loan_status=1, that have at least one existing overdue=true payment, ordered by the matching payments processed date.

Below is some sample data:

loan
+----+-------------+
| id | loan_status |
+----+-------------+
| 1  | 0           |
+----+-------------+
| 2  | 1           |
+----+-------------+
| 3  | 1           |
+----+-------------+
| 4  | 1           |
+----+-------------+
| 5  | 1           |
+----+-------------+

payment
+----+---------+----------------+---------------------+
| id | loan_id | overdue_status | processed_date      |
+----+---------+----------------+---------------------+
| 1  | 1       | f              | 2013-07-21 07:00:00 |
+----+---------+----------------+---------------------+
| 2  | 2       | f              | 2013-07-21 08:00:00 |
+----+---------+----------------+---------------------+
| 3  | 3       | f              | 2013-07-21 09:00:00 |
+----+---------+----------------+---------------------+
| 4  | 3       | t              | 2013-07-21 11:00:00 |
+----+---------+----------------+---------------------+
| 5  | 4       | f              | 2013-07-21 06:00:00 |
+----+---------+--------------------------------------+
| 6  | 4       | t              | 2013-07-21 10:00:00 |
+----+---------+----------------+---------------------+
| 7  | 4       | t              | 2013-07-21 13:00:00 |
+----+---------+----------------+---------------------+
| 8  | 5       | t              | 2013-07-21 10:30:00 |
+----+---------+----------------+---------------------+

With the sql query below I can find the matching loans, however, not ordered by processed date:

select id from loan where loan_status = 1 and exists(
    select id from payment where
        payment.loan_id = loan.id and payment.overdue_status = 't')

How can I change my sql query above (or make a new one) that orders the loans by their earliest overdue payment (processed_date)?

The wanted result using the data above should be:

+----+-------------+
| id | loan_status |
+----+-------------+
| 4  | 1           | // 2013-07-21 10:00:00
+----+-------------+
| 5  | 1           | // 2013-07-21 10:30:00
+----+-------------+
| 3  | 1           | // 2013-07-21 11:00:00
+----+-------------+
SELECT a.id, loan_status from loan_status a
inner join payment b on a.id = b.loan_id and b.overdue_status = 't' and loan_status = 1
GROUP BY a.id, loan_status, processed_date
ORDER BY processed_date

Use inner join
so you can also access the columns from the other table. (well, even if you don’t need it, your query would be better if you used an inner join. What you were doing was effectively an inner join anyway)

Hello, buddy!稿源:Hello, buddy! (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » In SQL: Command Unique Items by Related Timestamp Items

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录