Find rows with same id but different date

Question

0.00/5 (No votes)

See more:

Consider below table: Item: b , table: Order a

id   price     date          |   id     cid     date
1    100    2018-01-01       |   1      000    2018-02-15
1    200    2018-02-01       |   1      111    2019-01-01
1    300    2019-03-01       |   2      000    2017-04-01
2    100    2017-04-01       |   1      333    2020-01-01
2    200    2019-05-01       |   3      222    2018-05-01
3    300    2018-06-01       |   1      000    2020-01-01
3    400    2018-03-01       |

How can i get Order price each date like so:

a.id    a.cid     b.price      a.date
1       000        200         2018-02-15
1       111        200         2019-01-01
2       000        100         2017-04-01
1       333        300         2020-01-01
3       222        400         2018-05-01
1       000        300         2020-01-01

What I have tried:

SQL

SELECT a.id,a.cid,b.price,a.date FROM  Order a
LEFT JOIN Item b ON a.id = b.id
WHERE a.date = (SELECT MIN(b2.date)
FROM   Item b2
WHERE  b2.id  =  a.id 
AND    b2.date >= a.date)

Posted 11-Jan-19 6:19am

terzasek

Updated 11-Jan-19 7:35am

Add a Solution

Comments

Richard Deeming 11-Jan-19 13:26pm

REPOST
This is a slightly-reworded version of the question you asked yesterday:
https://www.codeproject.com/Questions/1274095/LEFT-JOIN-two-table-each-period-of-date[^]

If you want to edit your question to add more information, click the green "Improve question" link and update your question. Don't post the update as a new question.

terzasek 11-Jan-19 20:00pm

Ok, i will remove one of theme.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2019-01-11T07:35:00

As far as I can see this is the same query as in your previous questoin, just renamed columns.

In other words

SQL

SELECT a.id,
       a.cid,
       b.price,
       a.date
FROM         order a      
   LEFT JOIN item  b ON a.id = b.id
WHERE b.date = (SELECT MAX(b2.date)
                FROM   item b2
                WHERE  b2.id   =  a.id
                AND    b2.date <= a.date)