select distinct don't work as I thoght
select distinct don't work as I thoght
(OP)
Hi,
I have the following Tabels:
Movies
Id...Name...TruckBrandsFId..Set
1....Navn1..1...............8
2....Navn2..2...............10
3....Navn3..3...............3
4....Navn4..4...............5
5....Navn5..5...............7
6....Navn6..6...............2
7....Navn7..7...............9
8....Navn8..8...............1
9....Navn9..1...............4
10...Navn10.3...............6
TruckBrands
Id...Name
1....Navn1
2....Navn2
3....Navn3
4....Navn4
5....Navn5
6....Navn6
7....Navn7
8....Navn8
What I want:
Id...Name...TruckBrandsFId..Set
1....Navn1..1...............8
2....Navn2..2...............10
4....Navn4..4...............5
5....Navn5..5...............7
6....Navn6..6...............2
7....Navn7..7...............9
8....Navn8..8...............1
10...Navn10.3...............6
I hope someone can help me - I cann't figure out to make af select distinct on that......or what have I to do, to get the above?
Kind regards,
simsen
I have the following Tabels:
Movies
Id...Name...TruckBrandsFId..Set
1....Navn1..1...............8
2....Navn2..2...............10
3....Navn3..3...............3
4....Navn4..4...............5
5....Navn5..5...............7
6....Navn6..6...............2
7....Navn7..7...............9
8....Navn8..8...............1
9....Navn9..1...............4
10...Navn10.3...............6
TruckBrands
Id...Name
1....Navn1
2....Navn2
3....Navn3
4....Navn4
5....Navn5
6....Navn6
7....Navn7
8....Navn8
What I want:
Id...Name...TruckBrandsFId..Set
1....Navn1..1...............8
2....Navn2..2...............10
4....Navn4..4...............5
5....Navn5..5...............7
6....Navn6..6...............2
7....Navn7..7...............9
8....Navn8..8...............1
10...Navn10.3...............6
I hope someone can help me - I cann't figure out to make af select distinct on that......or what have I to do, to get the above?
Kind regards,
simsen

RE: select distinct don't work as I thoght
can you explain what you're trying to do?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: select distinct don't work as I thoght
I will try to explain:
I have one table; TruckBrands. In this table I have 8 entries (8 different truck brands).
My leading table; Movies I have a foreign key to TruckBrands
For each Truckbrand I now want to get the row in the Movies table, witch have that specific truckbrand AND Set is the higest number.
An example
Id Name TruckbrandFId Set
8....Navn8..8...............1
10...Navn10.8...............6
Here it should take Id 10 because Set is 6 and is higher than Set 1 for Id 8.
This should be done for each TruckBrand in the TruckBrands table.
I have to rows in Movies witch have have the same truckbrand but with different "Set". Now it should take the highest number from these two.
RE: select distinct don't work as I thoght
SELECT A.Id, A.Name, A.TruckBrandsFId, A.Set
FROM Movies A INNER JOIN (
SELECT TruckBrandsFId, MAX(Set) MaxSet FROM Movies GROUP BY TruckBrandsFId
) B ON A.TruckBrandsFId = B.TruckBrandsFId AND A.Set = B.MaxSet
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: select distinct don't work as I thoght