Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Nov 2006
    Posts
    33

    Unanswered: Forcing Specific Distinct Columns....

    Hi Guys,

    I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

    Is their a way to force 1 column to always be unique?

    Heres the query:

    Code:
    SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name', 
    (SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
    WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
    ) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
    CUSTOMER.customerForename AS 'Customer Forename', 
    CUSTOMER.customerSurname AS 'Customer Surname'
    FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
    WHERE ITEM.ItemID = ORDER_ITEM.ItemID
    AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
    AND ORDER_T.CustomerID = CUSTOMER.CustomerID
    GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
    CUSTOMER.customerForename, CUSTOMER.customerSurname
    ORDER BY Total_Purchased DESC
    The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

    Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

    ItemID
    8 36 30 Mathew Smith
    8 36 6 Tony Wattage

    Which is kinda annoying.... is there anyway i can prevent this?

    And also apart from the Where Joins... is there a more efficient way of writing this?

    thx for reading :-)

    --Philkills

  2. #2
    Join Date
    Nov 2006
    Posts
    33
    Does no one know any way around this?

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Phil,
    The SQL you have posted selecting:
    Code:
    ORDER_ITEM.ItemID AS 'Item ID',
    ITEM.ItemName AS 'Item Name', 
    (SELECT SUM(OrdItem2.ItemQuantity)
    ...does not match with the columns in the dataset you say you are getting:
    Code:
    ItemID
    8 36 30 Mathew Smith
    8 36 6 Tony Wattage
    People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
    Also, your problem sounds more like a data issue than a coding issue.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    Phil,
    The SQL you have posted selecting:
    Code:
    ORDER_ITEM.ItemID AS 'Item ID',
    ITEM.ItemName AS 'Item Name', 
    (SELECT SUM(OrdItem2.ItemQuantity)
    ...does not match with the columns in the dataset you say you are getting:
    Code:
    ItemID
    8 36 30 Mathew Smith
    8 36 6 Tony Wattage
    People are reluctant to respond to threads where the poster has not taken the time to accurately describe the problem.
    Also, your problem sounds more like a data issue than a coding issue.
    on the contrary, i left out the data on purpose as it was irrelevent.... i was only trying to point out an example of whats wrong.... in the "EXAMPLE"
    Code:
    ItemID
    8 36 30 Mathew Smith
    8 36 6 Tony Wattage
    The parts in bold should only appear once..... and the data it should select should be Mathew smith who purchased 30 of that item... 36 is the total amount sold, what i need the query to do... is to only display the person who bought the highest amount of the item that is part of the top 5 best selling items.

    another "EXAMPLE":

    ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
    1...........30......................30............ .............................Jim
    2...........20......................20............ .............................Jam
    3...........10......................10............ .............................Flim
    4...........5........................5............ ..............................Flam
    5...........2........................2............ ..............................Stam


    In this case.... only 1 person purchased the items in the top 5....

    However, if 5 people purchased the same item... the query would return:

    ItemID Total Sold Heighest Amount sold to 1 Customer Customers Name
    1...........30......................5............. ............................Jim
    1...........30......................5............. ............................Jam
    1...........30......................5............. ............................Flim
    1...........30......................5............. ............................Flam
    1...........30......................10............ ...........................Stam

    Notice how it returned the TOP 1 Item instead of the TOP 5 items?

    The data is correct.... the query is wrong.... which is why i asked if i could put a constraint on the data returned saying that ItemID MUST be unique.... unless ofcourse there is a better way to do it ;p

    I hope this more accurately describes the problem i am having. :-)

    --Philkills

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You are making this more confusing than it should be.

    Lets break it down into parts.

    Does this code give you the top five records that you want, without the customer information?
    Code:
    SELECT	TOP 5
    	ORDER_ITEM.ItemID AS 'Item ID',
    	ITEM.ItemName AS 'Item Name', 
    	SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
    FROM	ITEM
    	INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
    GROUP BY ORDER_ITEM.ItemID,
    	ITEM.ItemName
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    You are making this more confusing than it should be.

    Lets break it down into parts.

    Does this code give you the top five records that you want, without the customer information?
    Code:
    SELECT	TOP 5
    	ORDER_ITEM.ItemID AS 'Item ID',
    	ITEM.ItemName AS 'Item Name', 
    	SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased
    FROM	ITEM
    	INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
    GROUP BY ORDER_ITEM.ItemID,
    	ITEM.ItemName
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    yep that code gives the top 5 items ^^

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...and now you want the top customer for each of those items?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    ...and now you want the top customer for each of those items?
    correct....

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK. I lied. This is complicated, but we are almost there....
    Does this return the top CustomerID for each of those orders?:
    Code:
    SELECT	ORDER_ITEM.ItemID AS 'Item ID',
    	ITEM.ItemName AS 'Item Name', 
    	SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
    	CustomerID =
    		(SELECT	TOP 1
    			ORDER_T.CustomerID
    		FROM	ORDER_ITEM CUSTOMER_ITEM
    			INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
    			INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
    		WHERE	CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
    		GROUP BY ORDER_T.CustomerID
    		ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
    			ORDER_T.CustomerID)
    FROM	ITEM
    	INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
    GROUP BY ORDER_ITEM.ItemID,
    	ITEM.ItemName
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by blindman
    OK. I lied. This is complicated, but we are almost there....
    Does this return the top CustomerID for each of those orders?:
    Code:
    SELECT	ORDER_ITEM.ItemID AS 'Item ID',
    	ITEM.ItemName AS 'Item Name', 
    	SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
    	CustomerID =
    		(SELECT	TOP 1
    			ORDER_T.CustomerID
    		FROM	ORDER_ITEM CUSTOMER_ITEM
    			INNER JOIN ORDER_T ON CUSTOMER_ITEM.OrderID = ORDER_T.OrderID
    			INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
    		WHERE	CUSTOMER_ITEM.ItemID = ORDER_ITEM.ItemID
    		GROUP BY ORDER_T.CustomerID
    		ORDER BY SUM(CUSTOMER_ITEM.ItemQuantity) DESC,
    			ORDER_T.CustomerID)
    FROM	ITEM
    	INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
    GROUP BY ORDER_ITEM.ItemID,
    	ITEM.ItemName
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC
    Theres no table called CUSTOMER_ITEM.... However, i have edited it to return the top customerID correctly.

    Code:
    SELECT	ORDER_ITEM.ItemID AS 'Item ID',
    	ITEM.ItemName AS 'Item Name', 
    	SUM(ORDER_ITEM.ItemQuantity) AS Total_Purchased,
    	CustomerID =
    		(SELECT	TOP 1
    			ORDER_T.CustomerID
    		FROM	ITEM
    			INNER JOIN ORDER_ITEMOrdItem2 ON ITEM.ItemID = OrdItem2.ItemID
    			INNER JOIN ORDER_T ON OrdItem2.OrderID = ORDER_T.OrderID
    			INNER JOIN CUSTOMER ON ORDER_T.CustomerID = CUSTOMER.CustomerID
    		WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
    		GROUP BY ORDER_T.CustomerID
    		ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC,
    			ORDER.CustomerID)
    FROM	ITEM
    	INNER JOIN ORDER_ITEM ON ITEM.ItemID = ORDER_ITEM.ItemID
    GROUP BY ORDER_ITEM.ItemID,
    	ITEM_0510096.ItemName
    ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

  11. #11
    Join Date
    Nov 2006
    Posts
    33
    ok, so i can display the customer ID.... now how do i display the rest of the customer info .... ^_^

  12. #12
    Join Date
    Nov 2006
    Posts
    33
    Am i assuming that no one knows the answer?

    lol, kinda funny a problem that even professionals can't figure out

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Philkills
    lol, kinda funny a problem that even professionals can't figure out
    oh stop

    yes, there are a lot of professionals here

    what makes you think they want to be your personal free development center?

    ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

    good luck, phil

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Nov 2006
    Posts
    33
    Quote Originally Posted by r937
    oh stop

    yes, there are a lot of professionals here

    what makes you think they want to be your personal free development center?

    ask a question, get an answer, but keep modifying your questions to get more and more and more support as you struggle your way through something that seems to be quite challenging for you... well, there eventually comes a point where people will just go on to the next person

    good luck, phil

    Eh?, i asked 1 question in this thread.... and simply gave more info.... in my other posts....

    If your refering to my other questions that i've asked around the forum.... well, they are pretty much unrelated to this question. I have done a lot of research on SQL queries and the only questions i've asked are related to areas that i have found very difficult to find an answer to.

    So as a last resort i turned to this support forum, now i realise your doing this out of the kindness of your heart (i think) and for that im grateful. I too help people, but my area of expertise is with C++/Other languages, i on the other hand will help people regardless of the complexity of the problem, infact the more complex the better keeps things interesting.

    In regards to my original point about no one being able to solve the problem.... well, that was based on the posts inside this thread:

    Quote Originally Posted by blindman
    OK. I lied. This is complicated, but we are almost there....
    He admitted himself that this was complicated, still being new to SQL i do not yet know all of its limits.... but when a proffessional admits that something is complicated, i would say its a pretty good indication that it actually is complicated....

    When he never replied after that post.... i just assumed that he gave up and admitted defeat... i.e. its impossible to do..... atleast efficiently.


    So perhaps next time, before jumping in and making assumptions... you should consider all the facts and why i might have said such words.

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Philkills
    When he never replied after that post.... i just assumed that he gave up and admitted defeat... i.e. its impossible to do..... atleast efficiently.
    I......already put a lot of time in helping you with this.
    YOU....are impatient and rude.
    I.....did not log in over the weekend to do your work for you.
    YOU....can figure out the rest yourself, loser.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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