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?

1 comments

@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(
          'DISTINCT',
          sequelize.fn('substring', sequelize.col('countryName'), 1, 1),
        ),
        'letter',
      ],
    ],
    group: [sequelize.fn('substring', sequelize.col('countryName'), 1, 1)],
    raw: true,
  })

Related Questions

Sponsored Content

24 Answered Questions

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

16 Answered Questions

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

21 Answered Questions

[SOLVED] Get list of all tables in Oracle?

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

27 Answered Questions

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

26 Answered Questions

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

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

33 Answered Questions

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

22 Answered Questions

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

  • 2010-07-24 23:24:05
  • AP257
  • 601434 View
  • 931 Score
  • 22 Answer
  • Tags:   postgresql

27 Answered Questions

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

7 Answered Questions

[SOLVED] SQL Server SELECT INTO @variable?

Sponsored Content