Sql Query Group a range of numbers with low's and high's

Question

Hi all,

I have a table that looks something like below

street_id | address_number | address_direction | address_street | address_type ...
----------------------------------------------------------------------------------
1         | 121            | W                 | Fake           | St
1         | 131            | W                 | Fake           | St
1         | 200            | W                 | Fake           | St
2         | 321            | N                 | Fake           | St
2         | 131            | N                 | Fake           | St
2         | 500            | N                 | Fake           | St

Can anyone help me come up with a query that would help me display the data like below?

street_id | address_low_range | address_high_range | address_direction | address_street | address_type ...
----------------------------------------------------------------------------------
1         |  121              | 200                | W                 | Fake           | St
2         |  131              | 500                | N                 | Fake           | St

Thanks in advance

Answer 1

select
       street_id,
       direction,
       min(address_number) as address_low_range,
       max(address_number) as address_high_range
       address_street,
       address_type
from mytable
group by address_id, address_street, direction;

By the way, why are address_street and address_type in your table when assumably, that's what street_id refers to?

Answer 2

Off the top of my head:

select street_id, min(address_number), max(address_number), address_direction
from addresses
group by street_id, address_direction

Answer 3

you can use Min(address_number) and Max(address_number) to select the low and high ranges, then group by street_id, address_direction, address_street, address_type:

SELECT 
street_id, 
Min(address_number) as address_low_range,
Max(address_number) as address_high_range,
address_direction, 
address_street, 
address_type
FROM table_name
GROUP BY street_id, address_direction, address_street, address_type

Answer 4

Can you test this query

    select street_id, MIN(address_number) add_low,
MAX(address_number) add_high FROM your_table
Group by street_id

Answer 5

Use the MIN() and MAX() aggregate functions on the columns you want the min or max of. Make sure to include the other columns in a GROUP BY clause.