Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 73
  • Last Modified:

Return rows that match the first row

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
mgordon-spi
Asked:
mgordon-spi
1 Solution
 
Brian ChanDBACommented:
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
 
SharathData EngineerCommented:
;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
 
mgordon-spiAuthor Commented:
Thank you, this worked perfectly for me.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now