Solved

SQL Query - selecting top row

Posted on 2011-03-09
2
240 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
2 Comments
 
LVL 142

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
table fragmentation 40 75
Grid querry results 41 56
Group by and order by clause 28 36
IN with @variable 5 20
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now