Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: Selecting a distinct car with the lowest cost and displaying just one owner.

    Hello!

    I've attached a script to the tables I am "using", and a picture of the tables.

    I want this as the result:
    Code:
    carID, cost, name
    1       22      Berg
    The query I am currently having, which works, but not in the exact way I want it to...
    Code:
    SELECT t1.cost, t1.carID, t3.name
    FROM
    (
    	SELECT DISTINCT t1.cost, t1.carID, t2.carname
    	FROM 
    	(
    		SELECT MIN(cost) as cost, carID FROM carcost
    		WHERE carcost.carID = 1
    		GROUP BY carID
    	)as t1
    	INNER JOIN car as t2 ON t1.carID = t2.ID
    )as t1
    INNER JOIN carcost as t2 ON t1.carID = t2.carID AND t1.cost = t2.cost
    INNER JOIN carowner as t3 ON t2.carownerID = t3.ID
    But the trouble comes when two owners sells his car for the exact same price. Then this results in "duplicates". Lets say Jack and Casper also deliveres car 1, at a cost of 22. Then this query results in:
    Code:
    carID, cost, name
    1       22      Berg
    1       22,     Jack
    1       22      Casper
    I do not want "duplicates" like this, which ones of Berg, Jack or Casper should I pick to be returned? It does not matter, but, if you really need a criteria; the one with the lowest ID, the id in the "carowner"-table.

    Any suggestions?

    The "DISTINCT" in the "inner Select-statement", it returns only one lowest cost per car.
    Then when I try to join a seller to the cost and car, it results in multiple results per car... And of course I've tried different joins (outer, inner, right, left...)


    Edit: Ehm, quite lol...
    Code:
    DECLARE @tbl1 TABLE(cost int, carid int)
    DECLARE @tbl2 TABLE(carid int, name nvarchar(255))
    INSERT INTO @tbl1(cost, carid)
    (
    	SELECT DISTINCT t1.cost, t1.carID
    	FROM 
    	(
    		SELECT MIN(cost) as cost, carID FROM carcost
    		WHERE carcost.carID = 1
    		GROUP BY carID
    	)as t1
    )
    
    INSERT INTO @tbl2(carid, name)
    (
    	SELECT t1.carID, t1.name
    	FROM
    	(
    		SELECT TOP(1) t1.carid, t3.name FROM @tbl1 as t1
    		INNER JOIN carcost as t2 ON t1.carid = t2.carID AND t1.cost = t2.cost
    		INNER JOIN carowner as t3 ON t2.carownerID = t3.ID
    	) as t1
    )
    
    SELECT t1.carid, t1.cost, t2.name FROM @tbl1 as t1
    LEFT JOIN @tbl2  as t2 ON t1.carid = t2.carid
    Indeed, this returns what I want, but I feel I am abusing temp tables...

    Solved; but if you still have suggestions, without declaring temp tables...
    Attached Thumbnails Attached Thumbnails ThreeTables.jpg  
    Attached Files Attached Files
    Last edited by ManyTimes; 03-31-11 at 16:48.

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    ManyTimes. You may not realized it but you answered your own question.

    "the one with the lowest ID, the id in the "carowner"

    Replace lowest with MIN. Think about applying it to the result of your first query.

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Hehe...

    Yes, you are absolutely right.

    Code:
    SELECT t1.cost, t1.carID, t2.name FROM
    (
    	SELECT t1.cost, t1.carID, MIN(t3.carownerID) as carownerID
    	FROM 
    	(
    		SELECT MIN(cost) as cost, carID FROM carcost
    		WHERE carcost.carID = 1
    		GROUP BY carID
    	)as t1
    	INNER JOIN car as t2 ON t1.carID = t2.ID
    	INNER JOIN carcost as t3 ON t1.carID = t3.carID AND t1.cost = t3.cost
    	GROUP BY t1.cost, t1.carID
    )as t1
    INNER JOIN carowner as t2 ON t1.carownerID = t2.ID
    But, I think I'll go with the other solution anyways, due to the fact that I have already "moved on". But also because using "MIN" is quite expensive performance vice compared to TOP(1)...

    Unless?

    Thanks
    Last edited by ManyTimes; 03-31-11 at 18:18.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •