SQL Statement to select one record.


  • How do I write a sql statement to select one record from a table where multiple columns contain similar data.
    Table structure
    Column 1=RowId
    Column 2=page number
    Column 3=line number
    Column 4=Code
    Column 5=text

    C1   C2    C3   C4   C5
    1       9      1      B    John
    4      12     1      B    Peter

    How do I write sql to select the first row and process and then return to select the second row? My statement  needs to include a condition that selects all records with code B in Column 4.

    Friday, May 29, 2009 1:46 AM

All replies

  • Not sure I understand exactly what you're trying to do, but to select the first row only:

    SELECT TOP 1 * from MyTable WHERE C4='B' ORDER BY C1

    To select the n'th row: (first, second, etc).


    If you want to select the first row, process it, then select the second, process it, etc, you need a looping construct of some sort, such as a cursor.   But in general, this is a bad idea.  Think in terms of sets, and try to process all the rows in a single statement.

    Michael Asher
    • Edited by masher2 Friday, May 29, 2009 1:54 AM
    Friday, May 29, 2009 1:52 AM
  • Thanks for the input.
    I want to identify all records with B in C4 without knowing the row numbers. I then want to read each one and select the row id. I update a column in another table with the row id. Later I want to read this table and use the stored rowid to select each record.
    Thanks again

    I tried the top 1 but how do I get the second record?
    Friday, May 29, 2009 2:00 AM
  • To select a row and operate on it individually then fetch the next row would require a cursor or other looping process(not generally recommended if the processing could be accomplished using sets rather than a row by row approach)

    Here are some other samples of looping w/o cursors

    Friday, May 29, 2009 2:01 AM
  • > "I tried the top 1 but how do I get the second record? "

    With TOP 1, you can't.  You have to use the ROW_NUMBER window to get an arbitrary record.

    But from what you're telling me, you don't need to do any of this.  Just write a single update statement against table2 that selects all the row ids from table 1.     Later, use a select to join the two tables to reselect those rows.

    That's the power of SQL; you don't **need** a looping construct.
    Michael Asher
    Friday, May 29, 2009 2:07 AM
  • Thanks for the input.
    I tried Looping approach using while statement.

    declare @maxr as int, @ic as int, @row
    select @maxr =count(rowid)
    set @ic=1
    while @ic< @maxr
            select @row=rowid from TableA where C4='B'
             update TableB
                  set tRow=@row
           set @ic=@ic+1
          if @ic>@maxr




    This select only the last row.



    Friday, May 29, 2009 2:15 AM
  • I'm afraid the second query is illegal:


    You can't use windowed functions in a WHERE clause... only SELECT and ORDER BY.

    You'd have to put it in a CTE instead.

    Friday, May 29, 2009 2:16 AM
  • What do you mean by sets?
    When I use top 1, it only process the first one. When I use WHILE loop it processes only the las one because the select statement select both rows.
    Friday, May 29, 2009 2:20 AM
  • Oops, Brad's right.  Here it is in a subselect instead (to select row #2):

    (SELECT ROW_NUMBER() OVER(ORDER BY C1) as rn, *  FROM #MyTable WHERE C4='B' ) T
      WHERE rn=2

    Michael Asher
    • Proposed as answer by tizard Sunday, November 14, 2010 4:34 PM
    Friday, May 29, 2009 2:22 AM
  • > "What do you mean by sets?"

    I mean, instead of writing a loop that processes all the rows by looping through them, you write a single statement that does the entire job.

    For instance, if you want all rowids from Table1 into Table2, just do this:

    INSERT Table2 SELECT RowID from Table1 WHERE ....(whatever).....

    Then later, if you want to select all rows from Table1 that have a rowid present in Table2, you just join them:

    SELECT t1.* FROM Table1 t1 JOIN Table2 t2 ON t1.rowid = t2.rowid

    Michael Asher
    Friday, May 29, 2009 2:25 AM
  • It sounds interesting.
    I am going to try it.
    Thanks again.
    Friday, May 29, 2009 2:36 AM
  • Sorry but I am stuck again.
    I get error 'Subquery returned more than 1 value......'
    When I use the table created with only rowid in a join to the original table, I end up in the same place where I cannot isolate just one record.

    Friday, May 29, 2009 3:48 AM
  • Hey BRAD
    Thanks I tried this and I think it will work.
    Thanks all who pitched in.
    Friday, May 29, 2009 3:59 AM
  • Can I just say a big thanks to masher2, 5 hours later and an answer :)

    I wanted to select a row and get a value in a particular column and here's how I did it.

    comm = new SqlCommand("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY propertyID) as rn, * FROM propertysForRentTable) T WHERE rn=5", conn);
        reader = comm.ExecuteReader();
        while (reader.Read())
            rowSelected = reader["propertyID"].ToString();
        hidden.Text = (rowSelected);

    can you explain what the 'T' is in the SqlCommand before 'WHERE rn=5' please?

    cheers, Trev

    Sunday, November 14, 2010 4:40 PM
  • T is an alias of the subquery (derived table). You can use any letter you like, or, to make it clearer:

    FROM propertysForRentTable) as Derived WHERE rn=5
    -- Alternative 
    ;with cte as (select ROW_NUMBER() OVER (ORDER BY PropertyID) as rn, * from PropertyForRentTable)
    select * from cte where rn = 5


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Sunday, November 14, 2010 7:11 PM