CODEWITHSUNDEEP
sql
Ryan
Ryan

Reputation: 5546

SQL select only first with a given name

Houses

id OwnerId    street
-----------------------
1  owner1     street1
2  owner2     street1
3  owner3     street2
4  owner4     street2
5  owner5     street3

As you can see there can be more owners on the same street

I have another table where I want to insert the owners from Houses table, but only one from each street

Result of query:

table StreetRepresentant

ownerId    street 
------------------
owner1     street1
owner3     street2
owner5     street3

(ownerId is the PK for the table, if it matters. OwnerId is a unique in table Houses)

The query should be something like:

INSERT INTO StreetRepresentant (ownerId , street ) 
SELECT ownerId , street 
FROM Houses
--what should be here to get only 1 owner for each street? ...

Upvotes: 1

Views: 223

Answers (4)

Sai Kalyan Kumar Akshinthala
Sai Kalyan Kumar Akshinthala

Reputation: 11844

use Distinct keyword while selecting the rows, hope it will work.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166356

Have a look at something like

SELECT  Street,
        MIN(ID) FirstAvailableID
FROM    Houses
GROUP BY    Street

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

You can group by street, then use the MIN() function to return the lowest (as per your example) ownerId for each street.

SELECT MIN(ownerId) , street 
FROM Houses
GROUP BY street

Upvotes: 3

Sarfraz
Sarfraz

Reputation: 382666

INSERT INTO StreetRepresentant (ownerId , street ) 
SELECT ownerId , street 
FROM Houses h
GROUP BY h.street, h.ownerId

Upvotes: 0

Related Questions