Results 1 to 6 of 6

Thread: Query

  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Query

    Hi,

    Can someone help me out in the Query to find out the
    Last Record - 1 in the database

    Example

    I have a table which has a column called Date.

    DATE
    -----
    01 Jan 2004
    04 Jan 2004
    07 Jan 2004
    09 Jan 2004
    15 Jan 2004
    16 Feb 2004

    If i say Select Max(date) from table_name
    then i'll get 16 Feb 2004.

    but i want 15 Jan 2004, can anyone suggest a query on how to retrieve this

    Thanks
    Shankar

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    many ways:

    PHP Code:
    select max(datefrom table_name
    where date 
    < (Select Max(datefrom table_name); 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    67

    Query

    I have a requirement like this...

    I have a table that has a date field in it

    and i need to display 6 records from the table based on the following conditions.

    1) Always get the First Date

    2) Always get the Latest Date

    3) Always get the Latest Date 1 (it could be the same month or the previous month)

    4) Get the Dates for the Last 3 Month ends (Month End meaning 29 Feb, 31 March)

    5) If 4 does not satisfy 6 records condition get the previous 3 records of the Latest Date 1

    I need only SELECT statements and i dont need a stored proc for the same

    Thanks
    Shankar

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    didn't we already go through this Shankar?

    use the analytics I showed you from before.

    PHP Code:
    min(dateover () earliest_date,
    max(dateover () oldest_date,
    max(dateover (partition by to_char(date,'MMYYYY')) oldest_date_per_month 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Posts
    67

    Query

    Thats quiet different from what i wanted.

    But , i'll defiently work on it to get it through.

    Can u just give me the logic that runs through this SQL so that i can
    change according to my requirement

    Here's ur SQL
    ----------------------------------------------------------------------------
    SELECT
    total_each_month,
    to_char(DATE_OF_RUN, 'mm/dd/yyyy HH24:MI') rundate,
    plan_id
    FROM (
    SELECT
    COUNT(*) over (PARTITION BY PLAN_ID, TO_CHAR(rdng_dt,'MMYYYY')) total_each_month,
    MAX(DATE_OF_RUN) over (PARTITION BY PLAN_ID, TO_CHAR(rdng_dt,'MMYYYY')) ending,
    MIN(DATE_OF_RUN) over (PARTITION BY PLAN_ID, TO_CHAR(rdng_dt,'MMYYYY')) beginning,
    a.*
    FROM
    web_monitor_progress a
    WHERE
    a.PLAN_ID = 65404 AND
    a.DATE_OF_RUN BETWEEN (select min(DATE_OF_RUN) from web_monitor_progress where plan_id = 65404) AND
    TO_DATE('03292100 2359','MMDDYYYY HH24MI')
    )
    WHERE DATE_OF_RUN = ending OR DATE_OF_RUN = beginning
    ORDER BY DATE_OF_RUN DESC;

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    think of the count as a group-by where after the "over" clause
    you then "partition by" whatever you want to calculate

    you can have as many different count-groups as you want.

    a total count = count(*) over ()
    a count by month = count(*) over (partition by to_date(datecol,'MMYYYY')

    any groupings you might need in order to fill your requirement.
    then you can use case statements in your outer select to display the
    data you want

    ie:
    (case when total_records > 5 then [display whatever]
    when total_records < 6 then [display 3 records of max_date -1]
    end)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •