Results 1 to 8 of 8
  1. #1
    Join Date
    May 2012
    Posts
    3

    Question Unanswered: Confusing Order By Query

    Hi all,

    I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.

    For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
    Code:
    ID Type  Name	 Field
    1 Type1	Name1	(group1)
    2 Type2	Name2	(group2) -group2
    3 Type3	Name3	(group3)
    4 Type4	Name4	(group4)
    5 Type5	Name5	(group2) -group2
    6 Type6	Name6	(group6)
    But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
    Code:
    1 Type1	Name1	(group1)
    2 Type2	Name2	(group2) -group2
    5 Type5	Name5	(group2) -group2
    3 Type3	Name3	(group3)
    4 Type4	Name4	(group4)
    6 Type6	Name6	(group6)
    I can't seem to get my head around the proper way to accomplish this. Thanks for any pointers.
    Last edited by dannyboy35; 05-14-12 at 13:40. Reason: add detail

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dannyboy35 View Post
    I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
    What you describe is not logically possible.
    You can't sort any set by two distinct attributes simultaneously.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Show your sql and we'll try to help.

  4. #4
    Join Date
    May 2012
    Posts
    3
    I initially attempted:
    Code:
    select * from plants order by Type
    I then tried the following, but this orders the results by Field, which isn't what I'm looking for.
    Code:
    select * from plants order by Field, Type
    I suspect I may have to create a results table, then manipulate the data from there, but I'm not sure how to go about that. I am open to suggestions, I just don't know which avenue to head down.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    this orders the results by Field, which isn't what I'm looking for.
    It's strange statements.
    The query(... order by Field, Type) returned what you expected from your supplied sample data in your first post.

    If you want to say "which isn't what I'm looking for",
    you should add more sample data and expected results which support your statements.
    That is "order by Field, Type" doesn't return expected results.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Ordering by field then type ("type" is a reserved word, by the way) seems to work for me. At least with your sample data. Is the problem that you have a 'Type10', which is being ordered alphabetically, instead of numerically?
    Code:
     create table test1
     (id int,
      typename varchar(10),
      Name varchar(10),
      field varchar(10))
    go
    insert into test1 values
    	(1, 'Type1', 'Name1', '(group1)'),
    	(2, 'Type2', 'Name2', '(group2)'),
    	(3, 'Type3', 'Name3', '(group3)'),
    	(4, 'Type4', 'Name4', '(group4)'),
    	(5, 'Type5', 'Name5', '(group2)'),
    	(6, 'Type6', 'Name6', '(group6)')
    
    select *
    from test1
    order by field, typename
    
    id          typename   Name       field
    ----------- ---------- ---------- ----------
    1           Type1      Name1      (group1)
    2           Type2      Name2      (group2)
    5           Type5      Name5      (group2)
    3           Type3      Name3      (group3)
    4           Type4      Name4      (group4)
    6           Type6      Name6      (group6)

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    I then tried the following, but this orders the results by Field, which isn't what I'm looking for.
    If you posted the correct "output" in your example, this order by will return that result. . . The rows will be returned by "type" within "group" (i.e. group will be the primary sort key).

    If there something else amiss, you need to clarify.

  8. #8
    Join Date
    May 2012
    Posts
    3
    I'm not boding well going 36 hours without sleep, and I can see that I haven't been clear, nor have I been asking the right questions. My apologies, folks. Let me get some shut-eye, reevaluate my process, and I'll come better prepared in a day or two.

    Thanks for your patience!

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
  •