SQL Server – How to Skip Rows in a Query

sql serversql server 2014sql-server-2012

One I selected the query from my product table with an additional Row_Number() column (Num), I'm getting the answer like below (I limited only 20 rows to retrieve).

Query

SELECT * 
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ProductCode) AS Num, 
             ProductName
      FROM   BASE_Product 
      WHERE IsActive = 1) BASE_Product 
WHERE Num > 0 
AND   Num < (21)

Result

My requirement is how to skip each and every 3 rows and retrieve only 7 limit rows.

Expected result

I need the result such as below:

Best Answer

You can use the Modulo operator for this purpose.

Modulo
Returns the remainder of one number divided by another.

In this case:

WHERE (Num - 1) % 4 = 0

I've set up a sample, generating a series of 1K integers.

--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
select row_number() over(order by i) as val
into integers
from p1;
GO

1024 rows affected

select top 25 * 
from integers
where (val - 1) % 4 = 0
GO

| val |
| :-- |
| 1   |
| 5   |
| 9   |
| 13  |
| 17  |
| 21  |
| 25  |
| 29  |
| 33  |
| 37  |
| 41  |
| 45  |
| 49  |
| 53  |
| 57  |
| 61  |
| 65  |
| 69  |
| 73  |
| 77  |
| 81  |
| 85  |
| 89  |
| 93  |
| 97  |

dbfiddle here

Related Solutions

SQL Server 2014 – Calculate Balance for Each Row by Sequentially Subtracting Amount from Initial Sum

This is a "running total" type of problem: each row's total is calculated based on that row's value added to, or subtracted from, the previous row's total.

Since you are using SQL Server 2014, Transact-SQL has built-in syntax available to you to help you with getting the results.

Using the simplified model of "One deposit per month, many withdrawals per month", the SQL statement could go like this:

SELECT
  d.TotalAmount,
  w.WithdrawAmount,
  Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
FROM
  dbo.Deposit AS d,
  dbo.Withdrawal AS w
WHERE
  d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
  AND
  w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
;

The WHERE clause is supposed to filter the tables down to one deposit row from Deposit and the however many corresponding withdrawals from Withdrawal. If the tables support multiple accounts and the amounts need to be further related by account, you may want to replace the FROM clause with something like this:

FROM
  dbo.Deposit AS d
  INNER JOIN dbo.Withdrawal AS w ON d.AccountNumber = w.AccountNumber

The SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression calculates a running WithdrawAmount total for each row. So, the total increases with each row (sorted in the ascending order of WithdrawDate) – thus with each row an ever increasing amount is subtracted from TotalAmount, drawing Balance nearer to 0.

The above query will give you each row's balance but not the status. To get the status, you will need to reference the Balance value to compare it 0 and select a corresponding status string to return. Balance is a calculated column and in order to be able to reference it you need to nest the above query and reference Balance at the outer level. Nesting could be done with either a derived table or a common table expression (CTE). This query uses a CTE:

WITH balances AS
  (
    SELECT
      d.TotalAmount,
      w.WithdrawAmount,
      Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
    FROM
      dbo.Deposit    AS d,
      dbo.Withdrawal AS w
    WHERE
      d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
      AND
      w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
  )
SELECT
  TotalAmount,
  WithdrawAmount,
  Balance,
  Status = CASE WHEN Balance > 0 THEN 'StillAvailableYAY' ELSE 'ZeroBalanceOops' END
FROM
  balances
;

Of course, if you do not really need to return the balance – only to compare it to 0, then nesting is not needed and you can put the d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression directly into the CASE, replacing Balance.

Sql-server – split a row into multiple rows in sql server according to a specific column

You can generate the extra row(s) by doing an unpivot using a cross apply and the table value constructor.

select c.id,
       c.val
from dbo.a 
  inner join dbo.b
    on a.id = b.id
  cross apply (values(a.id, a.val),
                     (b.id, b.val)) as c(id, val)
where a.val <> b.val;