Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    23

    Unanswered: selecting the last two records

    Hi all. I've been having trouble with this. How do I select the last two records based on datetime.

    For instance, I have 30 records being inserted every five minutes. I want to select the last two records and compare them.

    I am using Sybase ASE 12.5 .

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    How do you determine the last-ness of the rows? Does each row have a different datetime value in a column, or do all 30 rows in a batch have the same datetime?

    Can you describe what you mean by "compare" in a bit more detail? Do you need to compare one column, the whole row, or something quite different?

    -PatP

  3. #3
    Join Date
    Nov 2003
    Posts
    23
    All 30 records will have the same datetime.

    Let's say I run a script that harvests disk space from a "df -k" command and inserts all of them into a database with the same datetime.

    Now, I want to compare the last two entries, based on the datetime, for a given filesystem. Thus allowing me to see if there are any filesystems that are growing to fast.

    This is just an example of what I want. To select the last two records based on datetime so that I can use them for comparison.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Ah, I wouldn't have even gotten close from your original description. What I think you want is something like:
    PHP Code:
    CREATE TABLE dkoutput (
       
    filesystem        VARCHAR(50)
    ,  
    kbytes        DECIMAL(150)
    ,  
    used            DECIMAL(150)
    ,  
    avail        DECIMAL(150)
    ,  
    capacity        VARCHAR(10)
    ,  
    mounted        VARCHAR(50)
    ,  
    asof            DATETIME
       
    )

    INSERT INTO dkoutput (
       
    filesystemkbytesused
    ,  availcapacitymounted
    ,  asofSELECT     '/dev/root'426421232015643222485691'47%''/',     '2004-03-23 17:00'
       
    UNION ALL SELECT '/proc',        81920,    40000,    41920'48%''/proc''2004-03-23 17:00'
       
    UNION ALL SELECT '/dev/root'426421232015643222485691'47%''/',     '2004-03-23 18:00'
       
    UNION ALL SELECT '/proc',        81920,    50000,    31920'61%''/proc''2004-03-23 18:00'

    SELECT a.filesystemMax(a.used), Max(b.used), CAST(100 * (Max(a.used) - Max(b.used)) / Max(b.used) AS DECIMAL(62))
       
    FROM dkoutput AS a
       JOIN dkoutput 
    AS b
          ON 
    (b.filesystem a.filesystem
          
    AND b.asof a.asof)
       
    GROUP BY a.filesystem

    DROP TABLE dkoutput 
    -PatP

  5. #5
    Join Date
    Nov 2003
    Posts
    23
    thanks, is there a simple way of pulling the last two records inserted into a table? i'm having a hard time decoding what you wrote and i need to know how to do it in other scenarios as well.

    sorry, i'm not that strong in SQL...but i'm trying

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The CREATE TABLE builds an empty table structure to accept the "df -k" output. The INSERT INTO stuffs some test data into the structure (basically what comes out of "df -k" plus the date/time it was run). The SELECT actually gets the data that is of interest, it is all you really care about. The DROP TABLE cleans up when the demo is done.

    If you focus your attention on the SELECT statement, you should be in business. I actually mangled the SELECT statement, in that it doesn't get exactly what I thought it would/should. You really need something more like:
    PHP Code:
    SELECT c.filesystemd.usede.used
    ,  100.0 * (e.used d.used) / e.used AS pct_change
          FROM 
    (SELECT a.filesystem
    ,     Max(a.used) AS last_asof
    ,     Max(b.used) AS prev_asof
          FROM dkoutput 
    AS a
          JOIN dkoutput 
    AS b
             ON 
    (b.filesystem a.filesystem
             
    AND b.asof a.asof)
          
    GROUP BY a.filesystem) AS c
       JOIN dkoutput 
    AS d
          ON 
    (d.filesystem c.filesystem
          
    AND d.asof c.prev_asof)
       
    JOIN dkoutput AS e
          ON 
    (e.filesystem c.filesystem
          
    AND e.asof c.last_asof
    The problem that most newcomers have with SQL is that you need to think in sets of data, not rows of data in order to get it to work well. That doesn't come naturally, but it is key to getting good performance from most database products.

    -PatP

Posting Permissions

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