Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

select distinct don't work as I thoght

Status
Not open for further replies.
simsen

simsen

Programmer
Joined
Nov 22, 2009
Messages
3
Location
DK
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 :-)
 
Sort by date Sort by votes
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.

 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Thank you very much - that was exactly what I needed :-)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question Question
SELECT COUNT distinct
Replies
5
Views
718
sal21
sal21
BobThornton
  • Locked
  • Question Question
Cross Join in an Insert Into
Replies
1
Views
584
carp
carp
szeiss
  • Locked
  • Question Question
Need to select the one value if it exists otherwise another value
Replies
4
Views
622
szeiss
szeiss
Tetol
  • Locked
  • Question Question
Update script is changing the number of records for the values updated each time I run the query
Replies
1
Views
587
carp
carp
BobThornton
  • Locked
  • Question Question
windows command in a sql script
Replies
1
Views
603
carp
carp

Part and Inventory Search

Sponsor

Back
Top