Multiple rows per condition - 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

General SQL Server Forums

New to SQL Server Programming

Multiple rows per condition

Author

Topic

quickie25
Starting Member

1 Post

Posted - 2014-05-31 : 14:31:22

I am using the following query to retrieve the top five customers from a MySQL table where 'accmanid' 1:

SELECT accmanid, custid, SUM(billone + billtwo) AS
total FROM customers WHERE accmanid = 1 ORDER BY total DESC LIMIT 5

This outputs five rows ordered by the total bill where accman = 1.

Here is my question:

How can I write the query to output five rows for EACH accmanid; Im not sure how to do such without the WHERE clause.

If you need more information or clarification, please ask and Il be happy to assist.

Thanks so much for your help and for this great site.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-05-31 : 20:08:58

This is MSSQL forum, so MSSQL way would be:

select accmanid
      ,custid
      ,total
  from (select accmanid
              ,custid
              ,sum(billone+billtwo) as total
              ,row_number() over (partition by accmanid order by accmanid,sum(billone+billtwo) desc) as rn
         from customers as c
        group by accmanid
                ,custid
       ) as c
 where rn<=5
 order by accmanid
         ,total

A bit more difficult on MySQL:

select accmanid
      ,custid
      ,total
  from (select accmanid
              ,custid
              ,total
              ,@rn:=if(@prev_accmanid=accmanid,@rn+1,1) as rn
              ,@prev_accmanid:=accmanid
          from (select accmanid
                      ,custid
                      ,sum(billone+billtwo) as total
                  from customers
                 group by accmanid
                         ,custid
               ) as c
              ,(select @rn:=1) as x
              ,(select @prev_accmanid:=0) as y
         order by accmanid
                 ,total desc
       ) as c
 where rn<=5
 order by accmanid
         ,total desc

GouravSaxena1987
Starting Member

23 Posts

Posted - 2014-06-02 : 00:49:12

In SQL-Server we can also use RANK() function

Regards,
Gourav Saxena
Data Warehouse Counsultant
GouravSaxena1987@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources