Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: Populating a table

    I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.


    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    How would you determine the order they should be sorted in? I'm assuming you do want them to be sorted in some intelligible fashion for the single dropdown list, right?
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I'm not sure why you would want to do this, but try

    order by NewID()

    Post the SQL statement you are using if you need more help.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    I will try to do that, the reason I am doing it as I have a list with different products, and some of them are from the same "family" and the others are not so insead of them appearing apha.. the client wants me to group them according to the family they belong to..

    Regards

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then you just need to sort your resultset by family before product. It's easy.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Guys,

    If I have a table with ProductId,ProductName,ProductCategory

    how can 'order by' whilst inserting values into my table,obvioulsy I can do when I am testing it through the Query analyzer but how can I store those details so when in my asp pages I open that drop list for that to appear sorted in a way I am after.

    sorry guys,I have a limited sql knowledge.....

    regards

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    select ProductId,
    ProductCategory + ': ' + ProductName as DropDownText
    from YourTable
    order by ProductCategory, Productname
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    That's not what I meant,but thanks for your help.....

  9. #9
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Why don't you give us say five sample rows. Then show us what you want to achieve on the front end. Say I have this table structure. I want to put data in this way. I want to get data out this way. We can then tell you exactly what you need to do, unless you already figured it out.

    It looks like you just want:

    DECLARE @Product TABLE(
    ProductId INT IDENTITY(1,1) PRIMARY KEY,
    ProductName VARCHAR(55),
    ProductCategory VARCHAR(55))

    INSERT @Product(
    ProductName,
    ProductCategory)

    SELECT 'diahrea','crap'
    UNION ALL
    SELECT 'miscellaneous','crap'
    UNION ALL
    SELECT 'peas','food'
    UNION ALL
    SELECT 'corn','food'

    SELECT
    ProductName,
    ProductCategory
    FROM
    @Product
    ORDER BY
    ProductCategory,
    ProductName
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  10. #10
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Ok if you are willing to help...I am not sure if I have figure it out,I have an idea but haven't tried it as yet....

    here is a part of the list I need to sort, and as you can see they have spaces in between.

    "Whole Blood
    Whole Blood Leucodepleted

    Red Cells
    Red Cell - Leucodepleted
    Red Cell - Buffy Coat Poor
    Paediatric Red Cell - Leucodepleted
    Washed Red Cell
    Washed Red Cell - Leucodepleted
    Apheresis Red Cell - Leucodepleted

    Platelet - Leucodepleted
    Pooled Platelet -Leucodepleted (Pool of 5)
    Pooled Platelet - (Pool of 4)
    Aph Platelet - Leudodepleted
    Paediatric Aph Platelet - Leucodepleted


    Red Cells
    Red Cell - Leucodepleted
    Red Cell - Buffy Coat Poor
    Paediatric Red Cell - Leucodepleted
    Washed Red Cell
    Washed Red Cell - Leucodepleted
    Apheresis Red Cell - Leucodepleted

    Platelet - Leucodepleted
    Pooled Platelet -Leucodepleted (Pool of 5)
    Pooled Platelet - (Pool of 4)
    Aph Platelet - Leudodepleted
    Paediatric Aph Platelet - Leucodepleted"


    I would like to have them appear in a drop down list like this(not necessary with a space,not sure if that will cause problems in asp???) I would like to give each of these little groups a name so users can get around easier, so it should be

    Blood Group ---as the name of this category and then the sub choices
    Whole Blood
    Whole Blood Leucodepleted

    Thanks

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the query should be a simple select as already suggested

    produce the formatted dropdown entries using your scripting language

    you mentioned this was for a web page, so presumably you're using ASP or ColdFusion or something

    yes, it is possible to produce the formatted results you want with sql alone, but the sql will the bloated, inefficient, ugly, and hard to maintain

    and since you said you have "limited sql knowledge" you would be better doing this in the script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    zobernjik, you are not explaining, or you are not understanding, 'cause everybody here looks at this and just sees a simple select statement solution.
    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
  •