By Michael

2019-12-02 23:04:05 8 Comments

I have a list of countries which will be separated by starting letter so for example when you click on 'A' it will make an API call to return all the countries beginning with 'A'.

However there are some letters that don't have any countries in our system, and these may change as we update out data.

I want to have a query that will let me know which letters do not have any countries that begin with them, so that I can disable them.

I can do this be running a findOne query for every single letter in the alphabet... but that is not neat or performant. Is there a way to get the data from a single query?


@Michael 2019-12-02 23:57:45

I am able to get the desired result by using a substring function within a distinct function.

 const result = await Countries.findAll({
    attributes: [
          sequelize.fn('substring', sequelize.col('countryName'), 1, 1),
    group: [sequelize.fn('substring', sequelize.col('countryName'), 1, 1)],
    raw: true,

Related Questions

Sponsored Content

26 Answered Questions

[SOLVED] Insert into ... values ( SELECT ... FROM ... )

24 Answered Questions

[SOLVED] How can I drop all the tables in a PostgreSQL database?

  • 2010-07-24 23:24:05
  • AP257
  • 676964 View
  • 1047 Score
  • 24 Answer
  • Tags:   postgresql

30 Answered Questions

[SOLVED] How do I perform an IF...THEN in an SQL SELECT?

34 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

16 Answered Questions

[SOLVED] Select first row in each GROUP BY group?

30 Answered Questions

[SOLVED] How to start PostgreSQL server on Mac OS X?

21 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2154580 View
  • 1102 Score
  • 21 Answer
  • Tags:   sql oracle

26 Answered Questions

[SOLVED] What is the difference between UNION and UNION ALL?

  • 2008-09-08 15:19:33
  • Brian G
  • 1331876 View
  • 1421 Score
  • 26 Answer
  • Tags:   sql union union-all

7 Answered Questions

[SOLVED] SQL Server SELECT INTO @variable?

Sponsored Content