Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Need help with SQL to correctly output results



I want to show the 1st, 2nd, 3rd, 4th, last date based on R-id (See Attachment.)
ee-img1.jpg
0
Jimmy_inc
Asked:
Jimmy_inc
  • 2
  • 2
1 Solution
 
lwadwellCommented:
Hi Jimmy_inc,

Access does not have the row_number() facility of many other dbms' ... so that makes this hard.

you could try something like ...
SELECT m2.R-id, m2.min1, m2.min2, min(t3.Ddate) as min3, m2.max1
from (SELECT m1.R-id, min1, min(t2.Ddate) as min2, m1.max1
      from (select R-id, min(Ddate) as min1, max(Ddate) as max1 from sheet1 group by R-id) m1
      left join sheet1 t2 on m1.R-id = t2.R-id and t2.Ddate > m1.min1
      group by m1.R-id, m1.min1, m1.max1) m2
left join sheet1 t3 on m2.R-id = t3.R-id and t3.Ddate > m2.min2
group by m2.R-id, m2.min1, m2.min2, m2.max1

this only goes to 1st, 2nd and 3rd ... but hopefully you get the idea

lwadwell
0
 
Jimmy_incAuthor Commented:
lwadwell can you recommend any books on this level of SQL ?

btw it worked perfectly!
0
 
lwadwellCommented:
Jimmy_inc,

Sorry to disappoint you but no, I cannot recommend any books on any level of SQL.  I have never read one.

lwadwell
0
 
Jimmy_incAuthor Commented:
Thanks
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now