Multiple Data Ranges in a Single Query SQL

Question

I have a student table now I want to write a query that return a single student in a specified ranges. For example if I provide two ranges (30 to 40) and (40 to 60), query should return me 2 students one which is in the range 30 to 40 and one in the range 40 to 60. How can I write a single query?

Please provide a sample data from student table. It is not clear if you have range in table and want just one record for each range or you have exact age or whatever value is in table — Kacper
– Kacper, Commented Oct 27, 2017 at 12:02
Lets say we have student id and percentage marks and I want to select a single student from each range, note that number of ranges may change they can be a parameter to the query. — pannu
– pannu, Commented Oct 27, 2017 at 12:04
Please dit your question and add some sample data and desired results for that data — Kacper
– Kacper, Commented Oct 27, 2017 at 12:08

Neodan · Accepted Answer · 2017-10-27 12:14:32Z

2

Use two selects with different ranges in where statement combined by UNION

Example:

(SELECT * FROM students WHERE ranges BETWEEN 30 AND 40 LIMIT 1)
UNION
(SELECT * FROM students WHERE ranges BETWEEN 40 AND 60 LIMIT 1)

edited Oct 27, 2017 at 12:14

Neodan

5,2742 gold badges31 silver badges39 bronze badges

answered Oct 27, 2017 at 11:57

Alex Kapustin

2,5191 gold badge15 silver badges15 bronze badges

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

4 Comments

pannu Over a year ago

What if we want to make the number of ranges configurable because in this solution we will have to write another select for every range.

Alex Kapustin Over a year ago

Yes, you have to build select for each range. There is no other way, afaik. But you can build dynamic sql, don't you ?

pannu Over a year ago

So we can take it as a sql query restriction.

pannu Over a year ago

Yes we can build on that thanks for pointing in the right direction it was really help full.

NikNik · Accepted Answer · 2017-10-27 12:11:05Z

0

You can use OR condition:

(range between 30 and 40) OR (range between 40 and 60).

and CASE:

CASE WHEN range between 30 and 40 then 'X' else 'Y' as CLASS

and then you can use GROUP BY to select 1 record for class so:

SELECT MAX(STUDENT_ID), CASE WHEN range between 30 and 40 then 'X' else 'Y' as CLASS
FROM MY_TABLE
GROUP BY CLASS

edited Oct 27, 2017 at 12:11

answered Oct 27, 2017 at 12:00

NikNik

2,3012 gold badges18 silver badges36 bronze badges

5 Comments

pannu Over a year ago

but how to make sure we have single entry in the result for each Range

Alex Kapustin Over a year ago

So if there are 2 records in range 30-40 ? :)

NikNik Over a year ago

I was missing: "one which is in the range 30 to 40 and one in the range 40 to 60." I edited my question. @AlexKapustin

Alex Kapustin Over a year ago

Quite tricky solution :) and if there are no requirements to show more than 1 record from range could be solution. Well done @niknik

Neodan Over a year ago

The solution with UNION is more better and optimal, especially if you have a big amount of entries (-;

Collectives™ on Stack Overflow

Multiple Data Ranges in a Single Query SQL

2 Answers 2

4 Comments

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

5 Comments

Related