SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Afrika
    Join Date
    Jul 2004
    Location
    Nigeria
    Posts
    1,737
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    index in a column

    hello,
    how do i get the index number in a column in my db.

    I tried using the charindex fucntion, but think i am doing the wrong thing

    any assistance ?
    thanks
    Afrika


    e..g

    item1
    item2
    item3
    item4
    item5

    if i want item 3, it should be 3

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, we need to step back a bit and start with the 30,000 foot perspective of what a relational database is, and then move on to your problem

    i promise to try to do this quickly

    okay, a relational database has tables, consisting of rows, made up of one or more columns

    columns don't have index numbers

    (indexes are used for query performance, and are a totally different concept)

    charindex is a string function, operating on a scalar (singular) value, so that's won't work here

    now, if we have --

    item1
    item2
    item3
    item4
    item5

    the only possible sequence we might have in mind is the sequence of those values, and we can only get this in a SELECT when we also use ORDER BY (because there is no such thing as physical sequence in a relational database, only ORDER BY sequence)

    in this context, the "index number" might be what is called the rank of this value

    depending on your database, producing ranking numbers is efficient or not, easy or not, because each database has different syntax

    however, since you are querying a specific column value, there's a reasonably efficient and easy way that will work in any database system, except if (*cough*mysql*cough*) subqueries aren't supported

    what you do is count the number of values that are greater than or equal, and this tells you the rank going from highest to lowest
    Code:
    select col 
         , ( select count(*)
               from yourtable
              where col >= this.col )  as rank
      from yourtable as this
     where col = 937
    note this assumes the ranking you want is from highest to lowest, so if you want lowest to highest, it's less than or equal

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    696
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    What do you mean by index number? There is no concept of positions in a RDBMS.


Bookmarks

Posting Permissions

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