Duplicate Values
-
When I run the following code why are all of the car colours returned? I am trying to find a way of returning the very first colour associated to a car:
create table #Car (ID INT, strMan varchar(30), Model varchar(30))
insert into #Car values (1, 'Ford', 'Fiesta')
create table #CarCol (ID INT, CarID INT, Colour varchar(30))
insert into #CarCol values (1, 1, 'Red')
insert into #CarCol values (1, 1, 'Blue')
insert into #CarCol values (1, 1, 'Green')
select a.ID, max(b.Colour )
from #Car as a
inner join #CarCol as b on a.ID = b.CarID
group by a.ID, b.Colour
-
create table #Car (ID INT, strMan varchar(30), Model varchar(30))
need to replace id in your group by with model and remove colour as below.
insert into #Car values (1, 'Ford', 'Fiesta')
create table #CarCol (ID INT, CarID INT, Colour varchar(30))
insert into #CarCol values (1, 1, 'Red')
insert into #CarCol values (1, 1, 'Blue')
insert into #CarCol values (1, 1, 'Green')
select Model, max(b.Colour )
from #Car as a
inner join #CarCol as b on a.ID = b.CarID
group by Model
-
It's because of your JOIN. Look at the data you're inserting in the CarCol tables:
ID CarID Colour
1 1 Red
1 1 Blue
1 1 Green
This means that the only thing that uniquely identifies each row is the Colour name. My guess is that the (CarID, ID) combo should uniquely identify each row. Also what do you mean by the "First" colour? First alphabetically? Or by ID? Here's a sample that lists the "First" colour of each car in the table. First color is defined here as the colour with the lowest ColourID number for that car:
CREATE TABLE #Car (
[ID] INT NOT NULL PRIMARY KEY,
strMan VARCHAR(30),
Model VARCHAR(30))
INSERT INTO #Car VALUES (1, 'Ford', 'Fiesta')
INSERT INTO #Car VALUES (2, 'Bat', 'Mobile')
CREATE TABLE #CarCol (
CarID INT NOT NULL,
ColourID INT NOT NULL,
Colour VARCHAR(30),
PRIMARY KEY (CarID, ColourID))
INSERT INTO #CarCol VALUES (1, 1, 'Red')
INSERT INTO #CarCol VALUES (1, 2, 'Blue')
INSERT INTO #CarCol VALUES (1, 3, 'Green')
INSERT INTO #CarCol VALUES (2, 1, 'Black')
INSERT INTO #CarCol VALUES (2, 2, 'Silver')
SELECT a.ID, b.Colour
FROM #Car AS a
INNER JOIN #CarCol AS b
ON a.[ID] = b.CarID
WHERE b.ColourID = (
SELECT MIN(ColourID)
FROM #CarCol
WHERE CarID = b.CarID)
DROP TABLE #Car
DROP TABLE #CarCol
If you want to make the "first colour" the color for a car that comes first alphabetically, change the WHERE clause to read:
WHERE b.Colour = (
SELECT MIN(Colour)
FROM #CarCol
WHERE CarID = b.CarID)
Unless you can guarantee that the "first color", however you define it, is the correct color you want pulled up, you might want to add another column (CHAR(1), 'Y'/'N') that contains a flag indicating that a color is, or is not, the "first color".
-
Jules/Mike, thank you both for your help the correct results are now being returned.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply