Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unanswered: Select Statement

    I want to create a query which does the following:
    based on table below
    Products |Categories| Attributes
    A |Big| 1
    A |Big| 1
    A |Big| 2
    A |Little| 2
    A |Little| 2
    A |Little| 5
    B |Big| 1
    B |Big| 1
    B |Medium| 3
    B |Medium| 3
    B |Medium| 3
    B |Medium| 8

    provide list
    Products |Categories| Attributes
    A |Big| 2
    A |Little| 5
    B |Medium| 8

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So, the maximum Attributes for each Category, and the Product(s) associated with that Category and Attribute value?

    Code:
    Select	YourTable.Products,
    	YourTable.Categories,
    	YourTable.Attributes
    From	YourTable
    	Inner Join --SubQuery
    		(Select	Categories,
    			Max(Attributes) Attributes
    		From	YourTable
    		Group by Categories) SubQuery
    		On YourTable.Categories = SubQuery.Categories
    		And YourTable.Attributes = SubQuery.Attributes
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2005
    Posts
    2

    sorry i should have used better example

    I need subset where Products and Categories are the same yet Attributes does not equal to other Attributes in subset
    Example in first subset
    A|BIG|1
    A|BIG|1
    A|BIG|2<- only record where attribute different

    in second subset
    A|Little|2
    A|Little|2
    A|Little|5<-only record where attribute different

    in third subset
    B|Big|1
    B|Big|1 no records to output because all the same

    Sorry for confusion I am a newby

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Different query, but not difficult:

    Code:
    Select	YourTable.Products,
    	YourTable.Categories,
    	YourTable.Attributes
    From	YourTable
    Group By YourTable.Products,
    	YourTable.Categories,
    	YourTable.Attributes
    Having	Count(*) = 1
    ...but what if there are two unique records in a subset? Such as:

    A|BIG|1
    A|BIG|1
    A|BIG|2 <- unique attribute
    A|BIG|3 <- another record with unique attribute
    A|BIG|4
    A|BIG|4
    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
  •