# How to isolate a previous row in a results set?

• ### 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
where fjobno='IT578-0000' and fcompqty<>0) V
Where V.RowNumber = 1```

Friday, December 21, 2018 4:00 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,SumFROM(
SELECT *,1 AS Cat
FROM CTE

UNION ALL

SELECT *,2
FROM CTE
WHERE Seq = 2)tORDER BY JobNo,shift,Cat DESC,[Date]```
Assuming you wanted one more row of penultimate row to be inserted for each group (jobno,shift)

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Edited by Friday, December 21, 2018 5:17 PM
• Marked as answer by 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
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
where fjobno='IT578-0000' and fcompqty<>0
) V
Where V.RowNumber = 2```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

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,SumFROM(
SELECT *,1 AS Cat
FROM CTE

UNION ALL

SELECT *,2
FROM CTE
WHERE Seq = 2)tORDER BY JobNo,shift,Cat DESC,[Date]```
Assuming you wanted one more row of penultimate row to be inserted for each group (jobno,shift)

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Edited by Friday, December 21, 2018 5:17 PM
• Marked as answer by 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

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

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;

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
• You're so obnoxious. I rather not get the answer than read through your wall of text.
Wednesday, December 26, 2018 2:52 PM