select distinct don't work as I thoght

[simsen]

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

 

[r937]

your sample data makes no sense

can you explain what you're trying to do?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[simsen]

Hi r937,

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.

 

[PHV]

You may try something like this:
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
FAQ181-2886

 

[simsen]

Thank you very much - that was exactly what I needed