Solved

SQL Query - selecting top row

Posted on 2011-03-09
2
247 Views
Last Modified: 2012-06-21
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
Comment
Question by:wppiexperts
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 63 total points
ID: 35085112
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
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 62 total points
ID: 35088488
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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question