Retrieving range in SQLServer
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10

Thread: Retrieving range in SQLServer

  1. #1
    Join Date
    Jun 2010
    Posts
    6

    Retrieving range in SQLServer

    Hi All

    Can any one let me know how can i get range of a particular column from database

    Eg:

    if i have a below table in data base

    id Name
    ---------------------------
    1 Item1
    2 Item2
    5 Item5
    6 Item6
    7 Item7
    9 Item9
    11 Item11
    ---------------------------

    i want to get out put like below one

    id
    ------------------
    1-2
    5-7
    9
    11
    ------------------

    I have heard it is possible but not sure how to do can any one please help me.

  2. #2
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,286

    Re: Retrieving range in SQLServer

    And what does this
    Quote Originally Posted by nmkishan22
    1-2
    mean?
    Or
    Quote Originally Posted by nmkishan22
    5-7

    Is it a simple subtraction or what?
    Victor Nijegorodov

  3. #3
    Join Date
    Jun 2010
    Posts
    6

    Re: Retrieving range in SQLServer

    its not minus it is the range of the ID present in the table

    ex. 1 to 2 are present in table
    5 to 7 are present in table
    9 is present in table
    11 is present in table


    so tht i can populate a combobox with different ids present in table else i need to get each item from table which wil be overload. If i have range i can manipulate in my application and get all the ids from the range i retrieved from table.

  4. #4
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,286

    Re: Retrieving range in SQLServer

    Unfortunately your explanation didn't make your "problem" more clear for me.
    What are you going to populate in a combobox: IDs or Names?
    What is wrong just to SELECT ID FROM [your table]?
    Victor Nijegorodov

  5. #5
    Join Date
    Jun 2010
    Posts
    6

    Re: Retrieving range in SQLServer

    If below is my table with 2 column id and Name

    id Name
    ---- ----------------------
    1 Item1
    2 Item2
    5 Item5
    6 Item6
    7 Item7
    9 Item9
    11 Item11
    ---- -----------------------

    if i write "Select Id from table" i wil get O/P like below

    id
    ---
    1
    2
    5
    6
    7
    9
    11
    ----

    But i wanted to get o/p like

    id
    ------------------
    1-2
    5-7
    9
    11
    ------------------

    Please help me out...

  6. #6
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,286

    Re: Retrieving range in SQLServer

    I think that SELECT ID FROM [your table]
    + write 5-7 line of code to find all the cases where the next record ID is not equal (the previous one + 1) would be much faster than waiting for answers how to do it only by means of SQL.
    Victor Nijegorodov

  7. #7
    Join Date
    Jun 2010
    Posts
    6

    Re: Retrieving range in SQLServer

    can you please give the code to do so. My id field will take value from 0 - 9999999999 so looping wil be a problem for me

  8. #8
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,286

    Re: Retrieving range in SQLServer

    No, I won't just try!
    It will be a very simple exercise for you to write such a code. Then you should test how fast/slow will it work with your very big table. If too slow - then and only then you should try to improve it.
    Victor Nijegorodov

  9. #9
    Join Date
    Sep 2011
    Posts
    3

    Re: Retrieving range in SQLServer

    try this
    SELECT * FROM tableName WHERE id IN (1,2,5,8)

  10. #10
    VictorN's Avatar
    VictorN is online now Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Wallisellen (ZH), Switzerland
    Posts
    17,286

    Re: Retrieving range in SQLServer

    Quote Originally Posted by bex_zex View Post
    try this
    SELECT * FROM tableName WHERE id IN (1,2,5,8)
    !. Do you expect OP is still needing our help? The thread is 15 months old!

    2. How your query could help the OP to get the following output
    Quote Originally Posted by nmkishan22
    i want to get out put like below one

    id
    ------------------
    1-2
    5-7
    9
    11
    ------------------
    Victor Nijegorodov

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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center