Selecting Top 10 distinct - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting Top 10 distinct

Author

Topic

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-06-18 : 06:27:18

Hi

I am trying to come with an SQL script that will do the following

UserID	Info2	BillStartDate	BillEndDate	AccountName	FirstName	LastName	Company	PhoneHome	PhoneWork	PhoneFax	Email
HongLin	8614958	17-Jan-09	14-Feb-09	zLivebox128K-12	Hong						
KeorapetseModise	5645728	15-Apr-09	14-May-09	zLivebox128K-12	Keorapetse						
SheikhMurad	3269659	15-May-09	14-Jun-09	zLivebox128K-12	Sheikh						
FumihiroSuzuki	3122211	15-Jan-09	14-Feb-09	zLivebox128K-12	Fumihiro						
KelebetsweKgomanyane	2406427	15-Mar-09	14-Apr-09	zLivebox128K-12	Kelebetswe						
KeorapetseModise	1541437	15-May-09	14-Jun-09	zLivebox128K-12	Keorapetse						
RobertShaibu	1101521	15-Mar-09	14-Apr-09	zLivebox128K-12	Robert						
NnyanaMotshwane	965521	15-May-09	14-Jun-09	zLivebox128K-12	Nnyana

But currently this is what I get

UserID	Info2	BillStartDate	BillEndDate	AccountName	FirstName	LastName	Company	PhoneHome	PhoneWork	PhoneFax	Email
KeorapetseModise	5645728	15-Apr-09	14-May-09	zLivebox128K-12	Keorapetse						
HongLin	5106249	17-Jan-09	14-Feb-09	zLivebox128K-12	Hong						
HongLin	3508709	15-Apr-09	14-May-09	zLivebox128K-12	Hong						
FumihiroSuzuki	3122211	15-Jan-09	14-Feb-09	zLivebox128K-12	Fumihiro						
KelebetsweKgomanyane	2406427	15-Mar-09	14-Apr-09	zLivebox128K-12	Kelebetswe						
SheikhMurad	1650265	15-May-09	14-Jun-09	zLivebox128K-12	Sheikh						
SheikhMurad	1619394	15-Apr-09	14-May-09	zLivebox128K-12	Sheikh						
KeorapetseModise	1541437	15-May-09	14-Jun-09	zLivebox128K-12	Keorapetse						
RobertShaibu	1101521	15-Mar-09	14-Apr-09	zLivebox128K-12	Robert						
NnyanaMotshwane	965521	15-May-09	14-Jun-09	zLivebox128K-12	Nnyana

If the same person is selected his Info2 should be added to gether and displayed as one e.g. see HongLin above
Below is the code

SELECT      TOP 10 dbo.Users.UserID, dbo.BillsSections.Info2, dbo.Bills.BillStartDate, dbo.Bills.BillEndDate, dbo.AccountTypes.AccountName, 
                        dbo.UserDetails.FirstName, dbo.UserDetails.LastName, dbo.UserDetails.Company, dbo.UserDetails.PhoneHome, dbo.UserDetails.PhoneWork, 
                        dbo.UserDetails.PhoneFax, dbo.UserDetails.Email
FROM          dbo.BillsSections INNER JOIN
                        dbo.Bills ON dbo.BillsSections.BillIndex = dbo.Bills.BillIndex INNER JOIN
                        dbo.Users ON dbo.Bills.UserIndex = dbo.Users.UserIndex INNER JOIN
                        dbo.AccountTypes ON dbo.Users.AccountIndex = dbo.AccountTypes.AccountIndex INNER JOIN
                        dbo.UserDetails ON dbo.Bills.UserIndex = dbo.UserDetails.UserIndex
WHERE      (dbo.BillsSections.Type = 100) AND (dbo.AccountTypes.AccountName = 'zLivebox128K-12') AND (dbo.Bills.BillStartDate >= '01/15/2009') AND 
                        (dbo.Bills.BillEndDate <= '06/15/2009')
ORDER BY dbo.BillsSections.Info2 DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-18 : 14:45:01

[code]SELECT UserID,
SUM(Info2) OVER (PARTITION BY UserID),
BillStartDate, BillEndDate, AccountName, FirstName, LastName,
Company, PhoneHome, PhoneWork, PhoneFax, Email
FROM
(
SELECT TOP 10
ROW_NUMBER() OVER (PARTITION BY dbo.Users.UserID ORDER BY dbo.Users.UserID) AS Seq,
dbo.Users.UserID, dbo.BillsSections.Info2, dbo.Bills.BillStartDate, dbo.Bills.BillEndDate, dbo.AccountTypes.AccountName,
dbo.UserDetails.FirstName, dbo.UserDetails.LastName, dbo.UserDetails.Company, dbo.UserDetails.PhoneHome, dbo.UserDetails.PhoneWork,
dbo.UserDetails.PhoneFax, dbo.UserDetails.Email
FROM dbo.BillsSections INNER JOIN
dbo.Bills ON dbo.BillsSections.BillIndex = dbo.Bills.BillIndex INNER JOIN
dbo.Users ON dbo.Bills.UserIndex = dbo.Users.UserIndex INNER JOIN
dbo.AccountTypes ON dbo.Users.AccountIndex = dbo.AccountTypes.AccountIndex INNER JOIN
dbo.UserDetails ON dbo.Bills.UserIndex = dbo.UserDetails.UserIndex
WHERE (dbo.BillsSections.Type = 100) AND (dbo.AccountTypes.AccountName = 'zLivebox128K-12') AND (dbo.Bills.BillStartDate >= '01/15/2009') AND
(dbo.Bills.BillEndDate <= '06/15/2009')
ORDER BY dbo.BillsSections.Info2 DESC
)t
WHERE Seq=1
[/code]

tkotey
Yak Posting Veteran

75 Posts

Posted - 2009-06-22 : 05:09:48

Thank you very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-22 : 10:28:03

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources