SQL Select for All Records That May Holds Specific Value

SQL select for all records that may holds specific value

---

So, you want to do a Google-like free text search over your database. This can be done but the performance will be Teh Suck! Google is fast because it has indexes on its indexes, duplicate data stores and generally optimizes everything for precisely this kind of search.

Anyway, here is a proof of concept using dynamic SQL and the Oracle data dictionary. Note that I restrict the columns to the type of data I want to search for i.e. strings.

SQL> set serveroutput on size unlimited
SQL> declare
  2      dummy varchar2(1);
  3  begin
  4      for r in ( select table_name, column_name from user_tab_cols
  5                 where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )
  6      loop
  7          begin
  8              execute immediate 'select null from '||r.table_name
  9                      ||' where '||r.column_name||' like ''%&search_value%'' '
 10                      ||' and rownum = 1'
 11                 into dummy;
 12              dbms_output.put_line('Found it in >>>'
 13                     ||r.table_name||'.'||r.column_name);
 14          exception
 15              when others then
 16                  -- bad practice ahoy!
 17                  null;
 18          end;
 19      end loop;
 20  end;
 21  /
Enter value for search_value: MAISIE
old   9:                ||' where '||r.column_name||' like ''%&search_value%'' '
new   9:                ||' where '||r.column_name||' like ''%MAISIE%'' '
Found it in >>>T23.NAME

PL/SQL procedure successfully completed.

SQL>

A more robust implementation might need to handle case, whole words, etc. If you're on 10g or higher then regular expressions could be useful, but combining regex and dynamic SQL is an, er, interesting prospect.

I repeat that performance is going to be Teh Suck! on a large data set. It is virtually impossible to tune, because we cannot index every column, and certainly not to support LIKE or similar fuzzy matches. An alternative approach would be to use XQuery to generate an XML representation of your data and then use Text to index it. Maintaining such a repository would be overhead, but the effort would be a sound investment if you need this functionality of a regular basis, especially in a production environment.

---

We can conduct a broader search across all the tables we have privileges on by using all_tab_cols instead.

for r in ( select owner, table_name, column_name from all_tab_cols
                   where data_type in ('VARCHAR2', 'CHAR', 'CLOB') )

Obviously we need to prefix the owning schema in the generated statement.

execute immediate 'select null from '||r.owner||'.'||r.table_name
                       ||' where '||r.column_name||' like ''%

SQL: Select all rows of the accounts which have certain value in a particular column

Solution 1

Select from column 1 and matching row from column 2 but Only if table 1 contains the right data in col3


SELECT ID1, ID2, Col3 FROM table1 LEFT JOIN table2 ON Table1.col3 = "a";

Solution 2

Select from column 1 and matching row from column 2 but Only if table 1 and table 2 contains the right data in col3


SELECT ID1, ID2, Col3 FROM table1 LEFT JOIN table2 ON Table1.col3 = "a" AND Table2.col3 = a;

SQL select rows with only a certain value in them

SELECT col2
FROM your_table
GROUP BY col2
HAVING MAX(col3) = 1 AND MIN(Col3) = 1

Or

SELECT a.col2 
FROM your_table a
WHERE a.col3=1 AND NOT EXISTS(SELECT *
                              FROM your_table b 
                              WHERE a.col2=b.col2 AND b.col3<>1)

Select all rows that has only specific value in records

You can use:

SELECT id
FROM cards
GROUP BY id
HAVING MIN(type) = MAX(type)

Demo here

SQL query to select rows where a column contains only specific values

Using aggregation, we can try:

SELECT id
FROM identities
GROUP BY id
HAVING MIN(child) = MAX(child) AND MIN(child) = 200;

The first condition of the HAVING clause asserts that a given id group of records has only a single child value. The second condition asserts that this single value is 200.

Select Records With Only One Value In A Column Where Multiple Are Possible

In standard (ANSI/ISO) SQL, a derived table is your friend. Here, we join the customer table against a derived table that produces the list of customers having only 1

select *
from customer c
join ( select customer_id
       from customer
       group by customer_id
       having count(program_name) = 1
     ) t on t.customer_id = c.customer_id
where ... -- any further winnowing of the result set occurs here

SQL select only rows with max value on a column

At first glance...

All you need is a GROUP BY clause with the MAX aggregate function:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

It's never that simple, is it?

I just noticed you need the content column as well.

This is a very common question in SQL: find the whole data for the row with some max value in a column per some group identifier. I heard that a lot during my career. Actually, it was one the questions I answered in my current job's technical interview.

It is, actually, so common that Stack Overflow community has created a single tag just to deal with questions like that: greatest-n-per-group.

Basically, you have two approaches to solve that problem:

Joining with simple group-identifier, max-value-in-group Sub-query

In this approach, you first find the group-identifier, max-value-in-group (already solved above) in a sub-query. Then you join your table to the sub-query with equality on both group-identifier and max-value-in-group:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Left Joining with self, tweaking join conditions and filters

In this approach, you left join the table with itself. Equality goes in the group-identifier. Then, 2 smart moves:

1. The second join condition is having left side value less than right value
2. When you do step 1, the row(s) that actually have the max value will have NULL in the right side (it's a LEFT JOIN, remember?). Then, we filter the joined result, showing only the rows where the right side is NULL.

So you end up with:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Conclusion

Both approaches bring the exact same result.

If you have two rows with max-value-in-group for group-identifier, both rows will be in the result in both approaches.

Both approaches are SQL ANSI compatible, thus, will work with your favorite RDBMS, regardless of its "flavor".

Both approaches are also performance friendly, however your mileage may vary (RDBMS, DB Structure, Indexes, etc.). So when you pick one approach over the other, benchmark. And make sure you pick the one which make most of sense to you.

How to return all records from table A , if any one of the column has a specific value in oracle sql?

select data.* from A data,
(select  Empid,count(lotname)
 from A 
group by Empid
having count(lotname)>1) MulLotEmp
where data.lotname='B'
 and data.Empid=MulLotEmp.Empid;

Check if this query solves your problem. In this I created a inner table first for your first requirement that emp with multiple lot, then I mapped this table with actual table with condition of input lot name.

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

---

Related Topics