Multiple SELECT statements SQLite

[Gandalf]

I have 5 SELECT statements and wondered if there is a way of combining them - or at least a more efficient way to do it.

$query  = 'SELECT * FROM talks WHERE id=1;';
$query  = 'SELECT * FROM talks ORDER BY date_added DESC LIMIT 1;';
$query  = 'SELECT * FROM talks ORDER BY num_views DESC LIMIT 1;';
$query  = 'SELECT * FROM talks WHERE id=3;';
$query  = 'SELECT * FROM talks WHERE id=21;';

Each SELECT is followed by

$result = $db->query($query);
foreach ( $result as $row ) {
  $id          = $row['id'];
  $description = $row['description'];
  $address     = $row['address'];
  $image       = $row['image'];
}

[r937]

i’m going to assume without looking it up that SQLite supports UNION as well as subqueries… so the answer is yes, you could probably do it all in one query

but i don’t think it would buy you too much

in fact, doing those “DESC LIMIT 1” queries is always going to produce just one row, whereas using a subquery for MAX(date_added) or MAX(num_views) would actually result in more than one row returned in case of ties

in my opinion, showing all ties is semantically more correct than just arbitrarily picking one of the ties

but that wouldn’t be equal to your queries

[Gandalf]

Thanks @r937