Solved

Return rows that match the first row

Posted on 2016-08-01
3
67 Views
Last Modified: 2016-08-01
I have a query that returns 1 row, it calculates total hours for each person and returns the top result. But I need to return any other rows that match the LastWeekTotal in the first row.

With UserStats (Username, LastWeekTotal) as (  
Select strDomainUsername, dbo.GetLastWeekBilling(strDomainUsername) from tblDomainAccountMap)
Select TOP 1 * from UserStats Order By LastWeekTotal DESC
0
Comment
Question by:mgordon-spi
[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
3 Comments
 
LVL 5

Accepted Solution

by:
Brian Chan earned 500 total points
ID: 41738262
Try this one:
WITH UserStats (Username, LastWeekTotal) AS
(  
	SELECT 
		strDomainUsername
		, dbo.GetLastWeekBilling(strDomainUsername) 
	FROM tblDomainAccountMap
) 
SELECT * 
FROM UserStats 
WHERE lastweektotal = (SELECT max(lastweektotal) from UserStats)
ORDER BY Username

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 41738298
;With UserStats (Username, LastWeekTotal) as (  
Select strDomainUsername, dbo.GetLastWeekBilling(strDomainUsername) from tblDomainAccountMap),
    UserStats2 AS (SELECT *,DENSE_RANK() OVER(ORDER BY LastWeekTotal) rn FROM UserStats)
Select Username, LastWeekTotal from UserStats WHERE rn = 1

Open in new window

0
 

Author Closing Comment

by:mgordon-spi
ID: 41738304
Thank you, this worked perfectly for me.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

635 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