How to get rows under a specific string value with SQL

Question

I want to create a query to fetch all the data under a specific value that is a string.

Let me use this table to make myself clear and let's call it staff.

+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| teacher | marcus      |  32  |   M   |
| nurse   | alice       |  27  |   F   | 
| gardener| leonard     |  26  |   M   | 
| doctor  | greg        |  45  |   M   |
| pilot   | rachel      |  22  |   F   | 
| driver  | jean        |  24  |   M   | 
+---------+-------------+------+-------+

How can I create a query that me returns all the values below 'doctor'?

Desired Result below

+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| pilot   | rachel      |  22  |   F   | 
| driver  | jean        |  24  |   M   | 
+---------+-------------+------+-------+

You did not name the table. You need to add the database version and name. assuming table name 'thetable', just use select distinct species from thetable or select species from thetable group by species — fcm
– fcm, Commented Mar 1, 2020 at 11:32
Which DBMS product are you using? "SQL" is just a query language, not the name of a specific database product. Why should I tag my DBMS — user330315
– user330315, Commented Mar 1, 2020 at 11:41
Sorry, I just saw I didn't explain my problem correctly. I've made the changes — Jose Almeida
– Jose Almeida, Commented Mar 1, 2020 at 14:46

Gordon Linoff · Accepted Answer · 2020-03-01 13:03:23Z

1

If you want all the values in the species column, you can use:

select distinct species
from t;

However, when I want this information, the count is usually helpful as well, so I tend to use group by:

select species, count(*)
from t
group by species;

answered Mar 1, 2020 at 13:03

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jose Almeida Over a year ago

Sorry, I just saw I didn't explain my problem correctly. I've made the changes

Ross Presser · Accepted Answer · 2020-03-02 15:08:39Z

You will have to make it clear what "below" is supposed to mean.

A table, by itself, has no inherent ordering according to the definition of SQL. If you just ask the database "Give me all the rows in the table" it is allowed to return them in any order it feels like. Both of these are permissible responses to the simple query SELECT * FROM staff:

+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| teacher | marcus      |  32  |   M   |
| nurse   | alice       |  27  |   F   | 
| gardener| leonard     |  26  |   M   | 
| doctor  | greg        |  45  |   M   |
| pilot   | rachel      |  22  |   F   | 
| driver  | jean        |  24  |   M   | 
+---------+-------------+------+-------+
+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| pilot   | rachel      |  22  |   F   | 
| driver  | jean        |  24  |   M   | 
| nurse   | alice       |  27  |   F   | 
| teacher | marcus      |  32  |   M   |
| doctor  | greg        |  45  |   M   |
| gardener| leonard     |  26  |   M   | 
+---------+-------------+------+-------+

If you add the ORDER BY clause, then you can depend on getting them in a known order, and then "below doctor" can mean something:

SELECT * FROM staff ORDER BY jobs
+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| doctor  | greg        |  45  |   M   |
| driver  | jean        |  24  |   M   | 
| gardener| leonard     |  26  |   M   | 
| nurse   | alice       |  27  |   F   | 
| pilot   | rachel      |  22  |   F   | 
| teacher | marcus      |  32  |   M   |
+---------+-------------+------+-------+

Now you can ask for all items "below" doctor -- more specifically, all items where the value of jobs is alphabetically after the value "doctor":

SELECT * FROM staff WHERE jobs > 'doctor' ORDER BY jobs
+---------+-------------+------+-------+
| jobs    | person      | age  | Gender|
+---------+-------------+------+-------+
| driver  | jean        |  24  |   M   | 
| gardener| leonard     |  26  |   M   | 
| nurse   | alice       |  27  |   F   | 
| pilot   | rachel      |  22  |   F   | 
| teacher | marcus      |  32  |   M   |
+---------+-------------+------+-------+

But this does not match your request. Unfortunately, there is NO ORDER BY clause that works with the data you gave that will put them in the order you gave.

The most common way out of this mess -- i.e., "PLEASE, database, keep the rows in the apparently random order that they were inserted!" -- is to add a DATE or INT column.

+---+---------+-------------+------+-------+
|id | jobs    | person      | age  | Gender|
+---+---------+-------------+------+-------+
| 1 | teacher | marcus      |  32  |   M   |
| 2 | nurse   | alice       |  27  |   F   | 
| 3 | gardener| leonard     |  26  |   M   | 
| 4 | doctor  | greg        |  45  |   M   |
| 5 | pilot   | rachel      |  22  |   F   | 
| 6 | driver  | jean        |  24  |   M   | 
+-------------+-------------+------+-------+

Now you can use this:

SELECT * FROM staff WHERE id > 4
+---+---------+-------------+------+-------+
|id | jobs    | person      | age  | Gender|
+---+---------+-------------+------+-------+
| 5 | pilot   | rachel      |  22  |   F   | 
| 6 | driver  | jean        |  24  |   M   | 
+-------------+-------------+------+-------+

Collectives™ on Stack Overflow

How to get rows under a specific string value with SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related