Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Red face Unanswered: select only the first row in a table

    Hi
    How can I return only the first row in a table? Simple enough question, but I can't seem to find the answer..
    /Helena

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not so simple a question

    how do you define "first" if physical placement in any particular order is not assured?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    hm...
    What I have is a table with two columns, a place and a IDnr. I wish to retrieve them in pairs, one row at the time. The order which I recieve them does not matter, but I have to go through all rows. I might be approaching this all wrong, any suggestions on what to do?
    /Helena

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, if you have to get them all, then that's a totally different problem from getting the "first" one, especially if order does not matter

    here, try this --
    Code:
    select place, IDnr from yourtable
    that will give you all your rows

    most likely they will be in the same order as entered, but order is not guaranteed without an ORDER BY clause in the SELECT statement

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    This will allow you to process your table one row at a time.

    Code:
    declare @IDnr ???, @Place ???
    select @IDnr = min(IDnr) from TableHelena
    while @IDnr is not null begin
      select @Place = place from TableHelena where IDnr = @IDnr
      print 'Place: ' + cast(@Place as varchar) + ' IDDnr: ' + cast(@IDnr as varchar)
      select @IDnr = min(IDnr) from TableHelena where IDnr > @IDnr
    end
    Of course you will need to fill in the datatype for the variables and test the code.

    What are you trying to do? Maybe your task could be done using a more efficient solution.
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Sep 2003
    Posts
    16
    I have a table with two columns, place and ID. This is just a staging table, and not part of the real database. I need to use the place and ID as variables in a stored procedure. The procedure takes one ID and one place at a time, check if they already exists in the real DB and insert them into the proper places.

    I thought I call the procedure with ID and place from the first row, and then call it with the parameters from the second row etc. But there's a great possibility a better solution is out there, hit me with it =)

    For those who is interested, my stored procedure look like this.

    CREATE PROCEDURE checkPlace
    @IDnr int,
    @place varchar(100)

    As

    declare @placeid int
    Select @placeid = placeID from place2 where place = @place

    if @placeid = null

    begin

    --if place does not already exists in DB, insert place

    INSERT INTO place2(placekey)
    values(@placekey)
    select * from place2

    -- myProcedure inserts placeID and IDnr into tablePlace

    execute myProcedure @placekey, @IDnr

    end

    -- if place exists in DB, insert placeID and IDnr into tablePlace

    else
    begin

    INSERT INTO tablePlace(OID, placeID)
    values(@IDnr, @placeID)
    select * from tablePlace
    end
    GO

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the better solution is to decalre a unique constraint in the real database, then go ahead and insert rows without bothering to check them -- let the database check them!!

    or, another approach...

    insert into targettable
    select place, IDnr from stagingtable
    where not exists
    ( select * from targettable
    where targettable.place = stagingtable.place
    and targettable.IDnr = stagingtable.IDnr )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    r937 not to sure about your first suggestion. While this will work I feel it is a bit sloppy. I think your 2nd suggestion is much better.

    2 inserts utilizing a left join would allow one to populate the place2 table and the tableplace table with the new data.
    Paul Young
    (Knowledge is power! Get some!)

  9. #9
    Join Date
    Oct 2003
    Posts
    706

    Lightbulb

    Some SQL's provide keywords like LIMIT to restrict the number of rows returned. Not applicable here AFAIK...

    However, also note that most SQL servers also don't bother to do all of the work in advance unless they must. They do enough to give you quick response-time to your query, and pursue the remaining results only when they must.

    Here's the rub: in order to obtain a meaningful notion of "first," you must use ORDER BY. But you would prefer not to thereby obligate the server to retrieve all the records just to be able to sort them just so that you can use (only...) the first one! Thus your query needs to be as absolutely-specific as possible, but also quick-to-run.

    You might indeed get several rows in your result-set, and you simply use only the first.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.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
  •