Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Oracle (https://www.askmehelpdesk.com/forumdisplay.php?f=443)
-   -   Get max of date field in a table for a particular month (https://www.askmehelpdesk.com/showthread.php?t=383642)

  • Aug 5, 2009, 03:48 AM
    stelbin
    Get max of date field in a table for a particular month
    Hi,
    I have data in a table as follows:

    Key date

    101 4/25/2006
    101 4/26/2006
    101 4/27/2006
    101 4/28/2006
    101 5/25/2006
    101 5/26/2006
    101 5/30/2006


    I need the max of date for each month. So my output expected is:
    101 4/28/2006
    101 5/30/2006

    How can I get this? Please help
  • Aug 5, 2009, 05:09 AM
    ROLCAM

    FOR:-

    101 4/25/2006
    101 4/26/2006
    101 4/27/2006
    101 4/28/2006
    101 5/25/2006
    101 5/26/2006
    101 5/30/2006

    TRY:-

    101 60425
    101 60426
    101 60427
    101 60428
    101 60525
    101 60526
    101 60530

    This gives you the opportunity of SORTING.
  • Aug 5, 2009, 05:16 AM
    ScottGem
    Quote:

    Originally Posted by ROLCAM View Post
    FOR:-

    101 4/25/2006


    TRY:-

    101 60425


    This gives you the opportunity of SORTING.


    Transposing data is not the best answer. Do you actually know anything about SQL?
  • Aug 5, 2009, 05:22 AM
    ScottGem

    What you need to do is use a subquery. You need to create a query that adds a column to identify the month. You then need to use the TOP 1 qualifier and sort by date in descending order and group by month. This query is then used as a subquery in an IN clause to restrict your result set.

  • All times are GMT -7. The time now is 04:55 PM.