all rows within a given column must match, for all columns

use aggregation

select ItemName
from myTable
where ItemLoc in ('LocA', 'LocB', 'LocC')
group by ItemName
having count(distinct itemloc) < 3

Suggestion : 2

All rows within a given column must match, for all columns,Pandas: How to replace Zero values in a column with the mean of that column, For all columns with Zero Value,Pandas Dataframe: Expand rows with lists to multiple row with desired indexing for all columns,Count how many rows within the same group have a larger value in a given column for each row in Pandas DataFrame

Given your example dataframe:

df = pd.DataFrame({
   'A': [1, 1, 1, 1, 1, 1, 1, 1, 1, 1],
   'B': [2, 2, 2, 2, 2, 2, 2, 2, 2, 2],
   'C': [3, 3, 3, 3, 3, 3, 3, 3, 3, 3],
   'D': [4, 4, 4, 4, 4, 4, 4, 4, 4, 4],
   'E': [5, 5, 5, 5, 5, 5, 5, 5, 5, 5]
})

You can use the following:

df.apply(pd.Series.nunique) > 1

Which gives you:

A False
B False
C False
D False
E False
dtype: bool

You then get:

A False
B True
C True
D False
E False
dtype: bool

You can use apply to loop through columns and check if all the elements in the column are the same:

df.apply(lambda col: (col != col[0]).any())

# A False
# B False
# C False
# D False
# E False
# dtype: bool

You can compare the entire DataFrame to the first row like this:

In[11]: df.eq(df.iloc[0], axis = 'columns')
Out[11]:
   A B C D E
0 True True True True True
1 True True True True True
2 True True True True True
3 True True True True True
4 True True True True True
5 True True True True True
6 True True True True True
7 True True True True True
8 True True True True True
9 True True True True True

then test if all values are true:

In[13]: df.eq(df.iloc[0], axis = 'columns').all()
Out[13]:
   A True
B True
C True
D True
E True
dtype: bool

In[14]: df.eq(df.iloc[0], axis = 'columns').all().all()
Out[14]: True

Suggestion : 3

Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. In this example, we have a table containing both the actual and budget revenues and profits for each application as shown below:,Find the column that corresponds to both the criteria selected in cells I20 and I21. Another MATCH() function can be used here. Unlike the regular case, your lookup value is derived from two cells. Combine these criteria using the & symbol.,It then finds the column number that corresponds to the criteria, “BudgetRevenue”, which is 3. The INDEX() function is now able to find the value using the numbers from the row and column arguments.,From this data, you need to create a report that returns the value corresponding to three criteria that the user selects:

When using the Index Match approach, the first thing you identify is the map or the area that contains the answer. Since this report needs to display the corresponding number from the criteria, the array used is C22:F31. The function at this point is written as:

Cell I22 = INDEX(C22: F31…

For the next argument in the INDEX() function, you need to determine how many rows you need to go down. The MATCH() function is used to find at what row number the lookup value is found. Since we want to use the selected App in cell H22 and matching it with cells B22:B31 which contains the Apps, the function now becomes:

Cell I22 = INDEX(C22: F31, MATCH(H22, B22: B31…

Since we want an exact match, we use 0:

Cell I22 = INDEX(C22: F31, MATCH(H22, B22: B31, 0)

Initially, you would think about using C20:F21 as your lookup array and write the formula as:

Cell I22 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, C20: F21, 0))

Since the MATCH() function can only handle single rows and columns, the simplest way to resolve the problem we had earlier is to use helper cells that combine the values in rows 20 and 21 into one row instead of two. This combination becomes the unique identifier of each column.

Cell C19 = C20 & C21

Go back to the last MATCH() function that used cells I20 and I21 as the lookup value. Instead of using C20:F21 as the lookup array, you can now use the new helper cells C19:F19.

Cell I22 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, C19: F19, 0))

However, clicking on CSE on a MATCH() function enables it to handle arrays. Write the same function as above, only instead of using the helper cells, revise the last MATCH() function to combine cells C20:F20 and C21:F21 using the & symbol.

Cell I26 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, C20: F20 & C21: F21, 0))

Notice that it places { } at the start and end of the function and becomes:

Cell I26 {
   = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, C20: F20 & C21: F21, 0))
}

Start off using the same functions for the most part except for the last MATCH() function:

Cell I29 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21…

In order to avoid the use of CSE, we need to use a function that can handle arrays. The INDEX() function can be used by putting the entire lookup area inside the INDEX() function. To do this, replace the last MATCH() function in Method 2 with:

INDEX(C20 & F20 & C21: F21, …

It now becomes:

Cell I29 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, INDEX(C20: F20 & C21: F21…

Use 0 as the row number

Cell I29 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, INDEX(C20: F20 & C21: F21, 0)…

Next, comply with the syntax of the MATCH() function where you need to specify the match type. Again, use 0 for an exact match. The final formula becomes:

Cell I29 = INDEX(C22: F31, MATCH(H22, B22: B31, 0), MATCH(I20 & I21, INDEX(C20: F20 & C21: F21, 0), 0))

Suggestion : 4

Last Updated : 28 Oct, 2021,GATE CS 2021 Syllabus

Query:

CREATE DATABASE geeks;

Syntax:

SELECT * FROM table_name WHERE column_name IN(list);

Suggestion : 5

In the first row in the sequence, the value of the price column must be less than 45.00.,For this measure, use COUNT(*). Because you are specifying COUNT(*) in the definition of a measure, the asterisk (*) specifies that you want to count all of the rows in a matching sequence (not all of the rows in the table).,In the second row, the value of the price column must be less than or equal to 90% of the value of the previous row.,In this example, for a given stock symbol, you want to find sequences of rows where the value in the price column decreases before increasing.

+ -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - +
|
COMPANY | MATCH_NUMBER | START_DATE | END_DATE | ROWS_IN_SEQUENCE | NUM_DECREASES | NUM_INCREASES |
   |
   -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - |
   |
   ABCD | 1 | 2020 - 10 - 01 | 2020 - 10 - 04 | 4 | 1 | 2 |
   |
   ABCD | 2 | 2020 - 10 - 04 | 2020 - 10 - 08 | 5 | 1 | 3 |
   + -- -- -- -- - + -- -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- + -- -- -- -- -- -- -- -- -- + -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- - +

SELECT * FROM stock_price_history
MATCH_RECOGNIZE(
   PARTITION BY company ORDER BY price_date MEASURES MATCH_NUMBER() AS match_number,
   FIRST(price_date) AS start_date,
   LAST(price_date) AS end_date,
   COUNT( * ) AS rows_in_sequence,
   COUNT(row_with_price_decrease.*) AS num_decreases,
   COUNT(row_with_price_increase.*) AS num_increases ONE ROW PER MATCH AFTER MATCH SKIP TO LAST row_with_price_increase PATTERN(row_before_decrease row_with_price_decrease + row_with_price_increase + ) DEFINE row_with_price_decrease AS price < LAG(price),
   row_with_price_increase AS price > LAG(price)
)
ORDER BY company, match_number;

create table stock_price_history(company TEXT, price_date DATE, price INT);

insert into stock_price_history values
   ('ABCD', '2020-10-01', 50),
   ('XYZ', '2020-10-01', 89),
   ('ABCD', '2020-10-02', 36),
   ('XYZ', '2020-10-02', 24),
   ('ABCD', '2020-10-03', 39),
   ('XYZ', '2020-10-03', 37),
   ('ABCD', '2020-10-04', 42),
   ('XYZ', '2020-10-04', 63),
   ('ABCD', '2020-10-05', 30),
   ('XYZ', '2020-10-05', 65),
   ('ABCD', '2020-10-06', 47),
   ('XYZ', '2020-10-06', 56),
   ('ABCD', '2020-10-07', 71),
   ('XYZ', '2020-10-07', 50),
   ('ABCD', '2020-10-08', 80),
   ('XYZ', '2020-10-08', 54),
   ('ABCD', '2020-10-09', 75),
   ('XYZ', '2020-10-09', 30),
   ('ABCD', '2020-10-10', 63),
   ('XYZ', '2020-10-10', 32);

MATCH_RECOGNIZE(
   PARTITION BY company ORDER BY price_date
   ...
)

.[A - Z] + [a - z] +