Status: Solved
Priority: Medium
Security: Public
Views: 260
Last Modified: 2012-06-21

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?

wppiexperts

Asked: 2011-03-09

Advertise Here

2 Solutions

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2011-03-09

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

LVL 50

LowfatspreadCommented: 2011-03-09

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

Question has a verified solution.

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

SQL Query - selecting top row

Join & Write a Comment Already a member? Login.

Featured Post