select distinct don't work as I thoght

Question

select distinct don't work as I thoght

simsen (Programmer)

(OP)

22 Nov 09 07:05

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 (TechnicalUser) · Answer 1 · 2009-11-22 07:43:53

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 (Programmer) · Answer 2 · 2009-11-22 10:37:40

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 (MIS) · Answer 3 · 2009-11-22 11:56:53

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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

simsen (Programmer) · Answer 4 · 2009-11-22 15:17:27

Thank you very much - that was exactly what I needed

Come Join Us!

Posting Guidelines

select distinct don't work as I thoght