Solved

SQL Query - selecting top row

Posted on 2011-03-09
2
245 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
get_systemdrive info from tsql? 1 23
SQL Server Configuration Manager WMI Error 11 41
SQL QUERY 3 33
Parsing this XML works but the other one doesn't 9 30
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 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