Need help with SQL to correctly output results

Posted on 2009-12-28
Last Modified: 2012-05-08

I want to show the 1st, 2nd, 3rd, 4th, last date based on R-id (See Attachment.)
Question by:Jimmy_inc
    LVL 25

    Accepted Solution

    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


    Author Comment

    lwadwell can you recommend any books on this level of SQL ?

    btw it worked perfectly!
    LVL 25

    Expert Comment


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


    Author Closing Comment


    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Remove Hyphens in Oracle SQL 5 35
    SQL help 5 42
    SP to delete duplicates 15 46
    query question 4 19
    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now