Way to Try Multiple Selects Till a Result Is Available

Way to try multiple SELECTs till a result is available?

LIKE without wildcard character is equivalent to =. Assuming you actually meant name = 'text'.

Indexes are the key to performance.

Test setup

CREATE TABLE image (
  image_id serial PRIMARY KEY
, group_id int NOT NULL
, name     text NOT NULL
);

Ideally, you create two indexes (in addition to the primary key):

CREATE INDEX image_name_grp_idx ON image (name, group_id);
CREATE INDEX image_grp_idx ON image (group_id);

The second may not be necessary, depending on data distribution and other details. Explanation here:

• Is a composite index also good for queries on the first field?

Query

This should be the fastest possible query for your case:

SELECT * FROM image WHERE name = 'name105' AND group_id = 10
UNION ALL
SELECT * FROM image WHERE name = 'name105'
UNION ALL
SELECT * FROM image WHERE group_id = 10
LIMIT  1;

SQL Fiddle.

The LIMIT clause applies to the whole query. Postgres is smart enough not to execute later legs of the UNION ALL as soon as it has found enough rows to satisfy the LIMIT. Consequently, for a match in the first SELECT of the query, the output of EXPLAIN ANALYZE looks like this (scroll to the right!):

Limit  (cost=0.00..0.86 rows=1 width=40) (actual time=0.045..0.046 rows=1 loops=1)
  Buffers: local hit=4
  ->  Result  (cost=0.00..866.59 rows=1002 width=40) (actual time=0.042..0.042 rows=1 loops=1)
        Buffers: local hit=4
        ->  Append  (cost=0.00..866.59 rows=1002 width=40) (actual time=0.039..0.039 rows=1 loops=1)
              Buffers: local hit=4
              ->  Index Scan using image_name_grp_idx on image  (cost=0.00..3.76 rows=2 width=40) (actual time=0.035..0.035 rows=1 loops=1)
                    Index Cond: ((name = 'name105'::text) AND (group_id = 10))
                    Buffers: local hit=4
              ->  Index Scan using image_name_grp_idx on image  (cost=0.00..406.36 rows=500 width=40) (never executed)
                    Index Cond: (name = 'name105'::text)
              ->  Index Scan using image_grp_idx on image  (cost=0.00..406.36 rows=500 width=40) (never executed)
                    Index Cond: (group_id = 10)
Total runtime: 0.087 ms

Bold emphasis mine.

Do not add an ORDER BY clause, this would void the effect. Then Postgres would have to consider all rows before returning the top row.

Final questions

Is there a generic solution for that?

This is the generic solution. Add as many SELECT statements as you want.

Of course it would come in handy when the search result is sorted by its relevance.

There is only one row in the result with LIMIT 1. Kind of voids sorting.

How does one populate a structure using multiple select statements

Your statement will return multiple result sets, If you are using DataReader, then you will get multiple results. Use DataReader.NextResult method to get next result set and show data accordingly.

If you are not concerned about DataReader then you can use DataSet and use a DataAdapater to fill out the results in multiple DataTables inside the DataSet.

MySQL Multiple SELECT queries with different WHERE clause on the same table

Use select union and not in

SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "A" AND city = "London"
union
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit = "B" AND city != "London
union
SELECT * from tbl_fruits WHERE fruit = "Apple" AND vit NOT IN ( "A" , "B" ) AND city != "London";

How do I Put Several Select Statements into Different Columns

You could use a series of queries unioned together. Kind of ugly, but it should work

SELECT  
  COUNT(user_id) AS '20100101'  
 ,NULL AS '20100102'  
 ,NULL AS '20100103'  
 ,NULL AS '20100104'  
 ,NULL AS '20100105'  
FROM  
  event_log_facts  
WHERE  
  date_dim_id=20100101  
UNION  
SELECT  
  NULL AS '20100101'  
 ,COUNT(user_id) AS '20100102'  
 ,NULL AS '20100103'  
 ,NULL AS '20100104'  
 ,NULL AS '20100105'  
FROM   
  event_log_facts  
WHERE  
  date_dim_id=20100102  
UNION  
SELECT  
  NULL AS '20100101'  
 ,NULL AS '20100102'  
 ,COUNT(user_id) AS '20100103'  
 ,NULL AS '20100104'  
 ,NULL AS '20100105'  
FROM  
  event_log_facts  
WHERE  
  date_dim_id=20100103  

ETC...

How to execute a 2nd query if the first one returns nothing?

For multiple rows

WITH fast AS (
   SELECT column1, column2
   FROM   mytable
   WHERE  <1st SET OF complex conditions>
   )
, slow AS (
   SELECT column1, column2
   FROM   mytable
   WHERE  NOT EXISTS (TABLE fast)
   AND    <2nd SET OF complex conditions>
   )
TABLE fast
UNION ALL
TABLE slow;

Test with EXPLAIN ANALYZE: you'll see (never executed) for the second query (CTE slow) if the first one (CTE fast) returns any rows. I think that's what you are aiming for.

TABLE fast is just (standard SQL) short syntax for SELECT * FROM fast. See:

• Is there a shortcut for SELECT * FROM?

Simpler for a single returned row

If both queries can only return a single row (or nothing), there is a simple, efficient solution without CTEs:

(
SELECT column1, column2
FROM   mytable
WHERE  <1st SET OF complex conditions>
)
UNION ALL
(
SELECT column1, column2
FROM   mytable
WHERE  <2nd SET OF complex conditions>
)
LIMIT 1;

Related:

• Select first record if none match
• Way to try multiple SELECTs till a result is available?

Is it possible to do multiple SQL select statements within one query? Both select statements would be on the same table

You should use a UNION statement.

SELECT hostname FROM mytable  
WHERE hostname  
    NOT LIKE '%obl%' AND 
    group NOT IN ('group1','group2','group3','group4','group5','group6','group7')  
    AND osname LIKE '%Windows%' 
    AND hostname not LIKE 'nic%'

UNION

SELECT hostname FROM mytable 
WHERE hostname 
    LIKE '%obl%' 
    AND group in ('group9','group0')

That will do it, but I think you could rethink those where conditions. I'll give it a thought and edit if necessary.

EDIT: You can achieve what you are trying to with a single query, no need for the union.

SELECT hostname FROM mytable 
WHERE (
    hostname NOT LIKE '%obl%' 
    AND group NOT IN ('group1','group2','group3','group4','group5','group6','group7') 
    AND osname LIKE '%Windows%' 
    AND hostname NOT LIKE 'nic%'
)
OR (
    hostname LIKE '%obl%' 
    AND group IN ('group9','group0')
)

How to get all selected values of a multiple select box?

No jQuery:

// Return an array of the selected opion values
// select is an HTML select element
function getSelectValues(select) {
  var result = [];
  var options = select && select.options;
  var opt;

  for (var i=0, iLen=options.length; i<iLen; i++) {
    opt = options[i];

    if (opt.selected) {
      result.push(opt.value || opt.text);
    }
  }
  return result;
}

Quick example:

<select multiple>
  <option>opt 1 text
  <option value="opt 2 value">opt 2 text
</select>
<button onclick="
  var el = document.getElementsByTagName('select')[0];
  alert(getSelectValues(el));
">Show selected values</button>

Retrieve data from stored procedure which has multiple result sets

It seems like there's no good simple way to do this, without a hack or a major paradigm shift. It looks like the best way is to just split out the original procs and end up with one more proc than before:

Old way:

create procedure dbo.GetSomething
as
begin
    select * from dbo.Person;
    select * from dbo.Car;
end;

New way:

create procedure dbo.GetPeople
as
begin
    select * from dbo.Person;
end;

create procedure dbo.GetCars
as
begin
    select * from dbo.Car;
end;

-- This gives the same result as before
create procedure dbo.GetSomething
as
begin
    exec dbo.GetPeople;
    exec dbo.GetCars;
end;

Then when I'm in a different proc and need both result sets, I'd just have to call them one at a time.

is there a pythonic way to try something up to a maximum number of times?

How about:

conn = MySQLdb.connect(host, user, password, database)
cursor = conn.cursor()
attempts = 0

while attempts < 3:
    try:
        cursor.execute(query)
        rows = cursor.fetchall()
        for row in rows:
            # do something with the data
        break
    except MySQLdb.Error, e:
        attempts += 1
        print "MySQL Error %d: %s" % (e.args[0], e.args[1])

---

Related Topics