• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

SQL First and last colour

Evening all
I know someone will look at this and have me an answer in 2 seconds but I have brain freeze at the moment and can't for the life of me figure it out...(it's been one of those weeks)

So I have this SQL database of products and I want to get some of the data in a nice View that I can then use. I've sorted most of it using Stuff... for XML but not sure how to get the first and last colour to populate all the way down the view for the given product

Many thanks in advance

 This is what my DB looks like
This is what I want in my View
  • 4
  • 4
1 Solution
Scott PletcherSenior DBACommented:
Would have to see the existing SQL to know specifically how to work that code in.  Could write working stand-alone code, but that won't necessarily work in the context of a different query :-).
LukeFileWalkerAuthor Commented:
Hi Scott

Here's what I have so far...

SELECT distinct OutTab.Product , OutTab.Colour ,
      Colour_List =
              STUFF ( ( SELECT ','+(InrTab.Colour)
                                    FROM [Product] InrTab
                                    WHERE InrTab.Product = OutTab.Product
                                     AND inrtab.Colour_Sequence = Min(outtab.Colour_Sequence)
                                     ORDER BY InrTab.Product, inrtab.Colour_Sequence
                                  FOR XML PATH(''),TYPE
                                , 1,1,SPACE(0))
      First_Colour = ???
      Last_Colour = ???
FROM [Product] OutTab
GROUP BY OutTab.Product , OutTab.Colour
Scott PletcherSenior DBACommented:
;WITH cte_Products AS (
SELECT DISTINCT Product, Colour, Colour_Sequence
FROM [Product]
    p_outer.Product, p_outer.Colour,
        SELECT ',' + p2.Colour
        FROM cte_Products p2
            p2.Product = p_outer.Product
        ORDER BY p2.Colour_Sequence
        FOR XML PATH('')
    ), 1, 1, SPACE(0)) AS colour_list,    
    prod_colours_min_max.colour_min, prod_colours_min_max.colour_max
FROM cte_Products p_outer
    SELECT Product,
        MAX(CASE WHEN row_colour_min = 1 THEN Colour ELSE '' END) AS colour_min,
        MAX(CASE WHEN row_colour_max = 1 THEN Colour ELSE '' END) AS colour_max
    FROM (
        SELECT Product, Colour,
            ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence ASC) AS row_colour_min,
            ROW_NUMBER() OVER(PARTITION BY Product ORDER BY Colour_Sequence DESC) AS row_colour_max
        FROM cte_Products
    ) AS derived
    GROUP BY Product
) AS prod_colours_min_max ON
    prod_colours_min_max.Product = p_outer.Product
ORDER BY p_outer.Product, p_outer.colour_sequence
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

LukeFileWalkerAuthor Commented:
Hi Scott
It works very well as a query but how do I stick that into a view as I don't think it'll allow the Order by clause in a sub query unless you use For XML?
Scott PletcherSenior DBACommented:
You can remove the ORDER BY at the end, I just threw that in there (I like to see things in logical order :-) ).

And of course remove the ; from before the WITH:

CREATE VIEW <view_name> AS
WITH cte_Products AS (
--ORDER BY --remove this
LukeFileWalkerAuthor Commented:
Slight mistake on my part. What I want in the view should look like below where for Prod02 the first colour is Black as the Colour _Sequence is 2 and the last colour should be Orange as the Colour _Sequence is 7. Also the Colour List should have been Black,Orange

Scott's query does this correctly but I'm not sure how to get it into a View

Scott PletcherSenior DBACommented:
>> Scott's query does this correctly but I'm not sure how to get it into a View <<

Please see my immediately previous post.  Just remove the leading ; and the ending ORDER BY from my query.
LukeFileWalkerAuthor Commented:
You are a Steely-eyed missile man Scott. Much thanks!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now