Results 1 to 8 of 8

Thread: sql query help

  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: sql query help

    I am querying the column 'name' from a table xxxx.

    It returned the following values:
    peter
    steve
    john
    andy


    But I need to have the result set in the following way:

    1 peter
    2 steve
    3 john
    4 andy

    How can i do this while querying names?
    Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    44

    A few ways

    One way I would do it, is have a column in that table called "NamePosition" this column with be of int datatype. And each name will have a position.

    Set Peter to position 1

    "UPDATE xxxx Set NamePosition = 1 WHERE name = 'Peter'"

    Do this will all your names and give them positions, steve: 2, john: 3, and andy: 4.

    Then you can query the results and ORDER BY NamePosition:

    "SELECT name FROM xxxx ORDER BY NamePosition"

    Hope that helped.

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    this is better

    select * from xxx
    order by yyy desc

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you know what number to assign to each row?

    The order of data in a table is irrelevant...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    Brett,

    Want to teach him about CURSORS?

    Originally posted by Brett Kaiser
    How do you know what number to assign to each row?

    The order of data in a table is irrelevant...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by SimSoph
    Brett,

    Want to teach him about CURSORS?
    I'd rather bang my head with a rock...


    NO CURSORS!

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(25))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'peter' UNION ALL
    SELECT 'steve' UNION ALL
    SELECT 'john' UNION ALL
    SELECT 'andy'
    GO
    
    DECLARE @x TABLE(Col0 int IDENTITY(1,1),Col1 varchar(25))
    
    INSERT INTO @x(Col1) SELECT Col1 FROM myTable99
    
    SELECT * FROM @x
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hey, i can name that tune with no cursors or temp tables:
    Code:
    select name
         , ( select count(*) 
               from xxxx 
              where name <= X.name
           ) as seq
      from xxxx as X
    order 
        by name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    sweeeeeeeeet......

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 varchar(25))
    GO
    
    INSERT INTO myTable99(Col1)
    SELECT 'peter' UNION ALL
    SELECT 'steve' UNION ALL
    SELECT 'john' UNION ALL
    SELECT 'andy'
    GO
    
    
    select Col1
         , ( select count(*) 
               from myTable99 
              where Col1 <= X.Col1
           ) as seq
      from myTable99 as X
    order 
        by Col1
    
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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