none
How to isolate a previous row in a results set? RRS feed

  • Question

  • I have query and I want to just get the previous from the last row in the set. For example if there's 7 rows in the result I only want to see what the 6th row is and only that row. I attempted to do a MAX(RowNumber) - 1 in a WHERE clause but it didn't work.

    Select 
    fdate,
    fsdatetime, 
    fedatetime,
    fshift,
    fjobno,
    SumForAllDates
    From
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0) V
    Where V.RowNumber = 1

    Friday, December 21, 2018 4:00 PM

Answers

  • ;With CTE
    AS
    
    (
    SELECT ...,
    ROW_NUMBER() OVER (PARTITION BY shift,JobNo ORDER BY [Date] DESC) AS Seq
    FROM (yourquery) q
    )
    SELECT Date,shift,JobNo,Sum
    FROM
    ( SELECT *,1 AS Cat FROM CTE UNION ALL SELECT *,2 FROM CTE WHERE Seq = 2
    )t
    ORDER BY JobNo,shift,Cat DESC,[Date]
    Assuming you wanted one more row of penultimate row to be inserted for each group (jobno,shift)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Friday, December 21, 2018 5:17 PM
    • Marked as answer by David9501 Wednesday, December 26, 2018 2:52 PM
    Friday, December 21, 2018 5:16 PM

All replies

  • You need to define on what basis you need the penultimate row i.e the sequence

    if its as given above what you need is this

    Select 
    fdate,
    fsdatetime, 
    fedatetime,
    fshift,
    fjobno,
    SumForAllDates
    From
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0
    ) V
    Where V.RowNumber = 2

    or if reverse order

    Select 
    fdate,
    fsdatetime, 
    fedatetime,
    fshift,
    fjobno,
    SumForAllDates
    From
    (Select  fdate,fsdatetime, fedatetime,fshift, fjobno,fcompqty ,
    SUM(fcompqty) Over(PARTITION BY fdate) As SumForThisDate,
    SUM(fcompqty) Over() As SumForAllDates,
    ROW_NUMBER() OVER (PARTITION BY fdate ORDER BY fdate DESC,fsdatetime DESC, fedatetime DESC,fshift DESC, fjobno DESC,fcompqty DESC) AS RowNumber
    from ladetail
    where fjobno='IT578-0000' and fcompqty<>0
    ) V
    Where V.RowNumber = 2


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 21, 2018 4:16 PM
  • This is what my result set looks like now.

    Date  |   shift |    JobNo| Sum

    12/20   1             123       500

    12/21   1             123       600

    12/22   1             123       20

    12/23   1             123       500

    This is what I'm trying to do. I just want to get whatever the row is before the last one.

    Date  |   shift |    JobNo| Sum

    12/20   1             123       500

    12/21   1             123       600

    12/22   1             123       20

    12/23   1             123       500

    12/22   1             123       20


    Friday, December 21, 2018 4:51 PM
  • ;With CTE
    AS
    
    (
    SELECT ...,
    ROW_NUMBER() OVER (PARTITION BY shift,JobNo ORDER BY [Date] DESC) AS Seq
    FROM (yourquery) q
    )
    SELECT Date,shift,JobNo,Sum
    FROM
    ( SELECT *,1 AS Cat FROM CTE UNION ALL SELECT *,2 FROM CTE WHERE Seq = 2
    )t
    ORDER BY JobNo,shift,Cat DESC,[Date]
    Assuming you wanted one more row of penultimate row to be inserted for each group (jobno,shift)

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    • Edited by Visakh16MVP Friday, December 21, 2018 5:17 PM
    • Marked as answer by David9501 Wednesday, December 26, 2018 2:52 PM
    Friday, December 21, 2018 5:16 PM
  • I'm confused on the 1 & 2 that you are using?
    Friday, December 21, 2018 7:06 PM
  • I'm confused on the 1 & 2 that you are using?

    Thats just for ordering the results in the way you want

    Just a dummy value for each query to ensure second query result comes back after first within each group of JobNo,shift


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, December 21, 2018 7:24 PM
  • >> I have a query and I want to just get the previous from the last row in the set. For example, if there are 7 rows in the result I only want to see what the 6th row is and only that row. <<

    You’re the kid in my SQL classes that I hate. You didn’t bother to do your homework or pick up even the most fundamental concepts of RDBMS. This leads you to ask absurd questions. The very first week of any class would stress how SQL is based on sets, and that sets have no <g class="gr_ gr_136 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="136" id="136">ordering</g>, so concepts like “previous” have no meaning. You still think that it’s a file based on an ordinal sequence.

    The second week of any SQL class, you would’ve heard about Codd’s 12 rules, one of which is the “information principle”, which states that all relationships (such as <g class="gr_ gr_96 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar only-del replaceWithoutSep" data-gr-id="96" id="96">an ordering</g>) must be represented by scalar values in the columns of a table.

    You became the rude poster who doesn’t put any DDL in his post and expect everybody else to figure out the keys, the constraints, the references, defaults and all the other things that make it possible to program.

    I also see that you still think in terms of punch cards from looking at the way you formatted and named the elements in your query. Back in the 1960s when I started programming. We always put one data element name on a card, just the way you put one data element name on a row. An SQL programmer would have put related data elements on the same line. An SQL programmer would not have put meaningless, vague prefixes on things. I’m going to make a very pornographic guess as to what a “f_date” means. The “F” should have been what ISO calls a data attribute name (birth_, employment_, etc.) and not a vague generic letter of the alphabet. This is why we decided to allow long names in SQL when we were writing the standards

    With minimal help from you, I make some guesses AS to what thing should have been named and what the relationship should be.

    >> I attempted to do a MAX(foobar_sorting_nbr) - 1 I’m also trying to figure WHERE clause but it didn't work. <<

    let’s try to bring your code into conformance with ISO 11179 naming rules, and formatted as if it was not being written on punch cards
    . Here’s my guess

    SELECT foobar_date, foobar_start_timestamp, foobar_end_timestamp, foobar_shift_nbr, foobar_job_nbr, foobar_comp_qty, 
    SUM(foobar_comp_qty) OVER(PARTITION BY foobar_date) 
        AS foobar_date_tot,
    SUM(foobar_comp_qty) OVER() AS foobar_tot, 
    ROW_NUMBER() OVER (PARTITION BY foobar_date 
                       ORDER BY foobar_start_timestamp,                    
                                foobar_shift_nbr) 
    AS foobar_sorting_nbr
    FROM La_Details
    WHERE foobar_job_nbr = 'IT578-0000' 
      AND foobar_comp_qty <> 0) AS V
    WHERE V.foobar_sorting_nbr = 1;

    please look at this expression:

    ROW_NUMBER() OVER (PARTITION BY foobar_date 
                       ORDER BY foobar_date, 
                                foobar_start_timestamp,                    foobar_end_timestamp,
                                   foobar_shift_nbr, foobar_job_nbr, 
                      foobar_comp_qty) AS foobar_sorting_nbr

    Since you are partitioning by “foobar_date”, it will be a constant in the ORDER BY clause. Likewise, foobar_job_nbr = 'IT578-0000' also makes the job number a constant. Isn’t there a relationship between the start and end timestamps, so this is a redundancy also? isn’t the quantity of something also just extra baggage, not related to the desired sorting order? 

    If you would actually follow the basic netiquette on the forum, and post the DDL along with some meaningful specs, then we really are able to help you. Do you want to try again?


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Monday, December 24, 2018 4:15 AM
  • I'm confused on the 1 & 2 that you are using?

    Why not apply the query statement to your real sample table data? So that you would understand what it is used for.

    create table Result01
    (
     [Date] date,
     [shift] int,
     JobNo varchar(30),
     [Sum] int
    )
    
    insert into Result01 values
    ('2018-12-20',1,'123',500),
    ('2018-12-21',1,'123',600),
    ('2018-12-22',1,'123',20),
    ('2018-12-23',1,'123',500)
    
    ;WITH CTE AS
    (
    select 
    ROW_NUMBER() OVER (ORDER BY [Date]) AS RN,
    [Date],
    [shift],
    JobNo,
    [Sum],
    COUNT(1) OVER () AS V_COUNT
    from Result01
    )
    SELECT [Date],[shift],JobNo,[Sum] FROM  Result01
    UNION ALL
    SELECT [Date],[shift],JobNo,[Sum] FROM  CTE 
    WHERE RN=V_COUNT-1
    
    --Output
    /*
    Date       shift       JobNo                          Sum
    ---------- ----------- ------------------------------ -----------
    2018-12-20 1           123                            500
    2018-12-21 1           123                            600
    2018-12-22 1           123                            20
    2018-12-23 1           123                            500
    2018-12-22 1           123                            20
    */

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, December 24, 2018 6:17 AM
    Moderator
  • You're so obnoxious. I rather not get the answer than read through your wall of text.
    Wednesday, December 26, 2018 2:52 PM