CODEWITHSUNDEEP
sqlsql-server
Smart_it
Smart_it

Reputation: 59

Select current row and all previous rows from a table

Let's take an example: I have a table named mytab with the following fields:

Name   Age
-----------
Jack    19
Josh    26
David   23

What I need to do is to select all the rows of this table and for every row, to select all its previous rows, so the output should be:

Name   Age
-----------
Jack    19
Jack    19
Josh    26
Jack    19
Josh    26
David   23

So the query result contains the first row, then first and second rows, then first, second and third rows.

Upvotes: 0

Views: 1672

Answers (2)

Martin Smith
Martin Smith

Reputation: 452967

A fun way of doing this which may perform better than self joining on a row numbered column is below db <> fiddle

WITH T AS
(
SELECT *,
       ROW_NUMBER() OVER (ORDER BY <ordering column> DESC) AS Weight
FROM #YourTable yt1
)
SELECT Id, Name
FROM T
CROSS APPLY (SELECT ROW_NUMBER() OVER (ORDER BY @@SPID) - Weight AS RN FROM STRING_SPLIT(SPACE(Weight - 1), ' ')) CA
ORDER BY RN,  <ordering column>

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

There is no first row in a table because they represent unordered sets. You need a column that specifies the ordering.

Your is really weird -- it can produce lots of rows (n * (n + 1) / 2 where n is the size of the table).

But you can enumerate the rows and self-join:

with tn as (
      select t.*, row_number() over (order by <ordering column>) as seqnum
      from t
     t
select tprev.name, tprev.age
from tn join
     tn tprev
     on tprev.seqnum <= t.seqnum
order by tn.seqnum, tprev.seqnum;

Upvotes: 2

Related Questions