Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.
COURSE of the MONTH
14 days, 16 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
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
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you!
In this Micro Tutorial, you'll learn yo…
- Microsoft Applications
- Windows 10
- Windows OS
- Fonts Typography
- Windows 7, Microsoft Word
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month14 days, 16 hours left to enroll
771 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.