Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite subquery select statement

I have two tables lets assume history and Favorites

  • Favorites schema is (id int primary key, isFav int)
  • History schema is (id int)

Favorites table has => songs with id and an int (0 or 1) and history table has => series of id which may be duplicates

I want a SQL statement for selecting ids from favorites which has value = 1 and order by number of occurrence(count) in history table (it may contain or not record with that id)

like image 622
Saiteja prasadam Avatar asked May 29 '16 16:05

Saiteja prasadam


People also ask

Does SQLite support subquery?

In SQLite, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

How do I SELECT two columns in SQLite?

To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.


1 Answers

This should work for you:

SELECT
x.id
, (
  SELECT 
     COUNT(y.id) 
  FROM y 
  WHERE y.id = x.id
  ) as YCount
FROM x
WHERE value = 1
ORDER BY  YCount;
like image 166
EoinS Avatar answered Nov 03 '22 23:11

EoinS