Having problems with query

Results 1 to 2 of 2

Thread: Having problems with query

  1. #1
    Join Date
    Dec 1969

    Default Having problems with query

    The data structure is as follows:<BR>This is arrived through the following query.<BR><BR>SELECT Emp#, Dept, count(*) <BR>FROM Employee<BR>group by Emp#, Dept<BR><BR>1001 Ops 4<BR>1001 Adv 6<BR>1002 Ops 2<BR>1005 Adv 6<BR>1005 Ops 8<BR><BR>Now, I need to get the records with maximum count.<BR>Desired result set is:<BR>1001 Adv 6<BR>1002 Ops 2<BR>1005 Ops 8<BR><BR>How should the query be modified to get the desired result ?<BR><BR>Thank you all in advance.

  2. #2
    Join Date
    Dec 1969

    Default RE: Having problems with query

    <BR>select Emp#,max(c) from (<BR>SELECT Emp#, Dept, count(*) c<BR>FROM Employee<BR>group by Emp#, Dept ) dt<BR>group by Emp#<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts