CODEWITHSUNDEEP
sql-server
zSynopsis
zSynopsis

Reputation: 4854

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

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

Upvotes: 0

Views: 786

Answers (5)

dnagirl
dnagirl

Reputation: 20446

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?

Upvotes: 2

Jeff Sharp
Jeff Sharp

Reputation: 999

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.

Upvotes: 1

TigerTiger
TigerTiger

Reputation: 10806

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

Upvotes: 1

Michael Abdelmalek
Michael Abdelmalek

Reputation: 176

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

Upvotes: 2

Jonas Elfström
Jonas Elfström

Reputation: 31438

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

Upvotes: 1

Related Questions