Getting all rows with common value in column A and specific value in column B
In MySQL I have table matcher_table with columns matcher and type. Records which are put here can have many different values for type column. One type matches another one only when value for matcher column is the same. Let's say I have to find all matching records for 5 types. Which would be the best approach/query in order to achieve this?
The table would be:
CREATE TABLE `matcher_table` ( `id` INT NOT NULL, `matcher` VARCHAR(45) NULL, `type` VARCHAR(45) NULL, PRIMARY KEY (`id`));
Now let's say we have this values in the table:
id | matcher | type 1 | match1 | type1 2 | match1 | type2 3 | match1 | type3 4 | match2 | type4 5 | match2 | type2 6 | match3 | type1 7 | match3 | type2 8 | match3 | type3
If I need to get matching data for types (type1, type2, type3), than I must get rows with ID 1, 2, 3, 6, 7, 8 (due to match1 and match3). If I need to get matching data for types (type1, type2, type3, type4) than I must get no records fulfilling this match.
I would recommend three
exists clauses -- because you want the original rows:
select mt.* from matcher_table mt where exists (select 1 from matcher_table mt2 where mt2.matcher = mt.matcher and mt2.type = 'type1' ) and exists (select 1 from matcher_table mt2 where mt2.matcher = mt.matcher and mt2.type = 'type2' ) and exists (select 1 from matcher_table mt2 where mt2.matcher = mt.matcher and mt2.type = 'type3' );
The advantage of this approach is that it avoids aggregation and it can make use of an index on
matcher_table(matcher, type). I would expect this to have very good performance in comparison to other approaches.
Extract all rows from a range that meet criteria in one column, Step 1 - Filter a specific column in cell range $A$2:$D$11 (INDEX($B$3:$E$12 , , $D$16, 1)< =$D$15)*(INDEX($B$3:$E$12, , $D$16, 1)> =$D$14) Extract records where all criteria match if not empty � Extract all rows that contain a value Did you get a reply on this I am experiencing the same problem. Hi @parry2k , . I need to get a count of a specific column, however it is counting the entire row individually giving us a count of 1 for every row because of the distinct values in each row.
You can try below query -
select * from `matcher_table` where matcher in (select matcher from `matcher_table` where type in ('type1', 'type2', 'type3') group by matcher having count(distinct type) = 3)
Here is the demo.
Lookup multiple values across columns and return , BOM b c6 2 NOT COMPLETED the sheet to be updated carries the 4 columns not in the same order as in the Note that all criteria must match in order to return 1. The INDEX function returns a value based on a row and column is the cell reference from which you want to get a specific value from. Example: In the below example first two rows has same value for first 3 columns.But they have different value for column DEF. So both these rows to be listed in output. But rows 2 and 4 has unique combination for first 3 columns but they have same values in DEF column.So not to be listed in output.
You could join to a sub-query that groups on the matcher field. And which only returns those with all the wanted types.
SELECT t.* FROM matcher_table t INNER JOIN ( SELECT matcher FROM matcher_table WHERE `type` IN ('type1', 'type2', 'type3') GROUP BY matcher HAVING COUNT(DISTINCT `type`) = 3 ) q ON q.matcher = t.matcher;
Look up values with VLOOKUP, INDEX, or MATCH, easy to use. You enter the same arguments, but it searches in rows instead of columns. Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel. Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A. 5 Get new features first. 1.Using groupby() which splits the dataframe into parts according to the value in column ‘X’ - df.groupby('X')['Y'].sum() 13. 2.Similarly, we can use Boolean indexing where loc is used to handle indexing of rows and columns-df.loc[df['X'] == 1, 'Y'].sum() 13 . 3.Query can also be used in order to filter rows you are interested in-
Return Multiple Match Values in Excel - Xelplus, Excel get multiple match results by looking up one value in Excel The most common function people use when finding items in an Excel list is VLOOKUP. We cannot simply increase the value of the row-num parameter by 1 every time we repeat the =IF(ROWS($F$5:F5)<=$F$4,INDEX($B$5:$B$14,AGGREGATE( 15,3� I have a pandas dataframe whose entries are all strings: A B C 1 apple banana pear 2 pear pear apple 3 banana pear pear 4 apple apple pear etc. I want to select all the rows that contain a certain string, say, 'banana'. I don't know which column it will appear in each time. Of course, I can write a for loop and iterate over all rows.
Use INDEX to lookup multiple values in a list. Learn Microsoft Excel , Excel's VLOOKUP function can't return multiple values. some other functions) to find all matching values in a list, and return a value from another column in the same row. Find all the row numbers to match our lookup value Get the array formula working in A11, and returning the correct result (Beans in my example). The picture above shows you a dataset in cell range B3:E12, the search parameters are in D14:D16. The search results are in B20:E22. The array formula in cell B20 searches for values that meet a range criteria (cell D14 and D15), the formula lets you change the column to search in with cell D16.
How to VLOOKUP multiple values in Excel with one , Let's say, you have the seller names in column A and the products they sold in column B, column A containing a few occurrences of each seller. Your goal is to get a list of all products sold by a given person. in some empty row, in the same or another worksheet. 2. In the opening Combine Rows Based on Column dialog box, you need to: (1) Select the column name that you will sum based on, and then click the Primary Key button; (2) Select the column name that you will sum, and then click the Calculate > Sum. (3) Click the Ok button.
- Show us some sample table data and the expected result - all as formatted text (no images.)
- have you tried self join?
- @ϻᴇᴛᴀʟ not sure if this works for my scenario. I need each matching record in a different row.
- @jarlh Did it. Sorry for being late, but I was forced to leave the computer for some time.
- The fastest one is by far the solution contained in accepted answer.
- @artaxerxe . . . I'm not surprised that this is faster, which is why I suggested it.