SQL Query - selecting top row

I'm building a query that joins multiple tables and I'm coming across results where the rows are identical except for a code field.


can I place some type of MIN or RANK function on the code field (based on the customer number) to find the lowest value within the main select statement, or do I need to do a subquery that finds the min first before its joined into the main select?
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this would do:
;with data as ( select t.*, row_number() over (partition by cust_name order by code) rn from yourtable t )
select * from data where rn = 1 

Open in new window

or this:
select t.*
  from yourtable t
 where t.code = ( select min(i.code) from yourtable i where i.cust_name = t.cust_name ) 

Open in new window

LowfatspreadConnect With a Mentor Commented:
select column1,column2,column3,....
 from ( select x.*
                    ,row_number() over (partition by customer_number order by TheCODEColumn ) as rn
              from (
                      ) as X
         ) as Y
 Where rn=1
 Order by YourQueriesOrderbyClausehere
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.