For each partition in query, do a case select

partition by
over(partition by sql w3schools)
partition by multiple columns
row_number() over (partition by in sql server)
db2 partition select query
partition by vs group by
partition by oracle
rank() over(partition by sql)

On the following query result coming from a CTE:

+---------+----------+----------+----------+---------------------+
|  STORE  |   DATE   |   TIME   | RESPONSE | ROW_NUMBER_BY_STORE |
+---------+----------+----------+----------+---------------------+
| Store 1 | 11/15/19 | 15:37:40 |     1    |          1          |
+---------+----------+----------+----------+---------------------+
| Store 1 | 11/15/19 | 15:37:40 |     1    |          2          |
+---------+----------+----------+----------+---------------------+
| Store 1 | 11/15/19 | 16:55:12 |     2    |          3          |
+---------+----------+----------+----------+---------------------+
| Store 1 | 11/15/19 | 16:55:12 |     2    |          4          |
+---------+----------+----------+----------+---------------------+

I've been trying to find out if there's any way to display on my query results on one single store:

  • if response 1 and 2 exist for the store, I want to only display response 1 on the results
  • if only response 1 exists for the store, display nothing.
  • if only response 2 exists for the store, display response 2.

I have a large amount of stores on my table of course, and I'm aware this may be achieved through a case statement, but the table from this query is not originally partitioned, and I tried to add a row_number() function with partition by the "store" column and wrap my query in a CTE to be able to query the row number function, but it seems I'm just not getting anywhere with it. So I was wondering if there could be any other way to run a query like this on each row group.

EDIT:

Here's a simplified version of my CTE. Not posting the original since it's rather a large query with lots of joins:

WITH CTE AS
(
SELECT
        STORE_ID,
        STORE,
        CONVERT (varchar, INPUT_DATE, 103) AS DATE,
        TIME, 
        RESPONSE,
        ROW_NUMBER() OVER (PARTITION BY STORE ORDER BY STORE) AS STORE


FROM TABLE_1

WHERE   INPUT_DATE BETWEEN 2019-11-15 AND 2019-11-15

)

SELECT *

FROM CTE

I'm going to suggest using window functions, but like this:

select cte.*
from (select cte.*,
             sum(case when response = 1 then 1 else 0 end) over (partition by store) as response_1,
             sum(case when response = 2 then 1 else 0 end) over (partition by store) as response_2
      from cte 
      where response in (1, 2)
     ) cte
where response_2 > 0 and
      (response_1 > 0 and response = 1 or
       response_1 = 0 and response = 2
      );

SQL PARTITION BY Clause overview, We will also explore various use case of SQL PARTITION BY. result set into partitions and perform computation on each subset of partitioned data. In the following query, we the specified ROWS clause to select the current� This article will cover the SQL PARTITION BY clause and, in particular, the difference with GROUP BY in a select statement. We will also explore various use case of SQL PARTITION BY. We use SQL PARTITION BY to divide the result set into partitions and perform computation on each subset of partitioned data. Preparing Sample Data

What about the following using OUTER APPLY()

DECLARE @Tab TABLE (STORE INT,DT DATE, TIME time(3), RESPONSE INT, ROW_NUMBER_BY_STORE INT)


INSERT INTO @Tab
VALUES 
(1,'11/15/19','15:37:40',1,1),
(1,'11/15/19','15:37:40',1,2),
(1,'11/15/19','16:55:12',2,3),
(1,'11/15/19','16:55:12',2,4),
(2,'11/15/19','16:55:12',2,1),
(2,'11/15/19','16:55:12',2,2),
(3,'11/15/19','16:55:12',1,1),
(3,'11/15/19','16:55:12',1,2)


SELECT t.Store
     , t.Dt
     , t.Time
     , CASE WHEN tt.DistinctResponse = 3 THEN 1
            WHEN tt.DistinctResponse = 2 THEN 2
            ELSE NULL END AS Response
     , t.Row_Number_By_Store
FROM @Tab t
OUTER APPLY(
            SELECT t2.Store, SUM(DISTINCT t2.Response) AS DistinctResponse
            FROM @Tab t2
            WHERE t2.Store = t.Store
            GROUP BY t2.Store
           ) tt

SQL PARITION BY Clause, Summary: in this tutorial, you will learn how to use the SQL PARTITION BY clause to change The PARTITION BY clause divides a query's result set into partitions. The window function is operated on each partition separately and recalculate for each partition. They cannot refer to expressions or aliases in the select list. The SQL ROW_NUMBER Function allows you to assign the rank number to each record present in a partition. In this example, we show you how to Select First Row from each SQL Group. The following SQL Query will. First, partition the data by Occupation and assign the rank number using the yearly income.

Here's a method that also starts from a CTE.

It uses the window versions of MIN & MAX. And a trick with a modulus to calculate a flag.

WITH CTE_DATA AS
(
  SELECT 
    STORE
  , CAST(dt AS DATE) AS [DATE]
  , CAST(dt AS TIME) AS [TIME] 
  , RESPONSE
  , ROW_NUMBER_BY_STORE
  FROM (VALUES
  ('Store1','2019-11-15 15:37:40',1,1),
  ('Store1','2019-11-15 15:37:40',1,2),
  ('Store1','2019-11-15 16:55:12',2,3),
  ('Store1','2019-11-15 15:37:12',2,4),
  ('Store2','2019-11-16 15:37:40',1,1),
  ('Store2','2019-11-16 15:37:40',1,2),
  ('Store3','2019-11-17 16:55:12',2,3),
  ('Store3','2019-11-17 15:37:12',2,4)
  ) v(STORE, dt, RESPONSE, ROW_NUMBER_BY_STORE)
)
, CTE_DATA2 AS
(
  SELECT *
  , CAST(IIF((
      RESPONSE
    + MIN(RESPONSE) 
      OVER (PARTITION BY STORE, [DATE]) 
    + MAX(RESPONSE) 
      OVER (PARTITION BY STORE, [DATE]))%2 = 1, 0, 1
    ) AS BIT) AS Flag
  FROM CTE_DATA
)
SELECT STORE, [DATE], [TIME], RESPONSE, ROW_NUMBER_BY_STORE
FROM CTE_DATA2
WHERE Flag = 1
ORDER BY STORE, [DATE], ROW_NUMBER_BY_STORE;
GO
STORE  | DATE                | TIME     | RESPONSE | ROW_NUMBER_BY_STORE
:----- | :------------------ | :------- | -------: | ------------------:
Store1 | 15/11/2019 00:00:00 | 15:37:40 |        1 |                   1
Store1 | 15/11/2019 00:00:00 | 15:37:40 |        1 |                   2
Store3 | 17/11/2019 00:00:00 | 16:55:12 |        2 |                   3
Store3 | 17/11/2019 00:00:00 | 15:37:12 |        2 |                   4

db<>fiddle here

Grouping Data using the OVER and PARTITION BY Functions , The article describes how to use the Over and Partition By functions to retrieve partitioned data. Run the following command in your query window: of each gender and the sum of the values in the total_score column for each gender. USE schooldb SELECT gender, count(gender) AS Total_Students,� The SQL Case statement is usually inside of a Select list to alter the output. What it does is evaluates a list of conditions and returns one of the multiple possible result expressions. For instance, let’s see how we can reference the “AdventureWorks2012” database and show an example of a SQL Case statement.

Partition By Case Statement Oracle, Integration where condition will partition case oracle dba. Supporting your Storing hierarchical query as columns within case? Rebase widget Enter your publication or measure values by case statement within each test. Bit more about if� The CASE expression evaluates a list of conditions and returns one of the multiple possible results. You can use a CASE expression in any statement or clause that accepts a valid expression. For example, you can use the CASE expression in statements such as SELECT, UPDATE, or DELETE, and in clauses like SELECT, WHERE, HAVING, and ORDDER BY.

MySQL Partitioning :: 5 Partition Pruning, Suppose a partitioned table t1 is created by this statement: When the optimizer can make use of partition pruning in performing this query, execution In the second case, the optimizer evaluates the partitioning expression for each value in� SQL PARTITION BY clause overview. The PARTITION BY clause is a subclause of the OVER clause. The PARTITION BY clause divides a query’s result set into partitions. The window function is operated on each partition separately and recalculate for each partition. The following shows the syntax of the PARTITION BY clause:

MySQL Partitioning :: 3.2 LIST Partitioning, As in partitioning by RANGE , each partition must be explicitly defined. Unlike the case with partitions defined by range, list partitions do not need to be declared mysql> TRUNCATE h2; Query OK, 1 row affected (0.00 sec) mysql> SELECT� The partitions to be checked are specified by the issuer of the statement, unlike partition pruning, which is automatic. Whereas partition pruning applies only to queries, explicit selection of partitions is supported for both queries and a number of DML statements.

Comments
  • The dataset that your CTE returns has duplicate records already. You might be better off showing us your CTE to start with (or a simplified version of it).
  • @GMB I updated the question. my original query is quite large and has several inner joins, what I posted anyway I believe should work out.
  • I'm testing this solution right now. Just a question , I've never seen the syntax SELECT whatever .* - In this scenario, what does it mean?
  • @HectorSoto . . . It means: "select all the columns that the table alias refers to".
  • please pardon my ignorance, so would @tab be like a temporary table?
  • Yes - technically a table variable. You can replace that with the name of your CTE.