• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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.

Ex:
CUSTA 100
CUSTA 101

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?
0
wppiexperts
Asked:
wppiexperts
2 Solutions
 
Guy Hengel [angelIII / a3]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

0
 
LowfatspreadCommented:
select column1,column2,column3,....
 from ( select x.*
                    ,row_number() over (partition by customer_number order by TheCODEColumn ) as rn
              from (
                           YourcurrentqueryExceptitsOrderbyClausehere
                      ) as X
         ) as Y
 Where rn=1
 Order by YourQueriesOrderbyClausehere
0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now