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.
Course Of The Month
2 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SQL Query - selecting top row
Posted on 2011-03-09
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?
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?
[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
Active today
this would do:
or this:
;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
or this:
select t.*
from yourtable t
where t.code = ( select min(i.code) from yourtable i where i.cust_name = t.cust_name )
Featured Post
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:
…
Suggested Courses
Course of the Month2 days, 18 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
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.