Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2004
    Posts
    12

    Question Unanswered: Select rows by one for each value

    Tell me please how to select from a table only rows with different value of one of fields (n_pr field). But I need also to select some other fields where the values can be different for one n_pr field's value and select only first value of them. For getting list of n_pr field's values I can use derived table of query with DISTINCT operator but I don't know how to join with other fields of the table to get only one row for each value of joinning field n_pr. I am using SQL Server 2000 and this DBMS can use TOP operator to select only one row but all the ways I've tryed affect all the query and I get one row at all instead of one for each n_pr field's value. Also I need in this query to connect one more table. So for efficiency it better to do it in one query or in one stored procedure that return a recodset as it needs.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select n_pr 
         , someotherfield
      from yourtable as X
     where someotherfield =
         ( select max(someotherfield) 
             from yourtable 
            where n_pr = X.n_pr )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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