Group By Help - 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

Group By Help

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2011-01-13 : 18:38:20

I was wondering if i could get some help here, with my sql syntax below. Any help would be great.

I have a table called v_employee_active, that has a few columns. My problem is, I have multiple records of the same person, and i want to get the highest number "rowcount" record for each record. For example, if my table has 2 id with 100, it should see both of these, and then look at the rowcount id and pick the highest, and display it. This could be the same if they had 3 or 4 or more accounts with same id, i just wanted record data for highest rowcount id.

-tablename v_employee_active
-columns
rowcount
id
firstname
lastname
schoolname
schoolid
positionname

Here is some sample data.
1 | 100 | joe | fox | joeschool | 45 | janitor
2 | 100 | joe | fox | joeschool | 45 | teacher
3 | 101 | joel | fox | joeschool | 48 | sub
4 | 102 | joey | fox | joeschool | 49 | janitor
5 | 103 | joesph | fox | joeschool | 51 | teacher
6 | 103 | bob | fox | joeschool | 44 | councelor
7 | 104 | tom | fox | joeschool | 41 | janitor

Here is what i would like to see.
2 | 100 | joe | fox | joeschool | 45 | teacher
3 | 101 | joel | fox | joeschool | 48 | sub
4 | 102 | joey | fox | joeschool | 49 | janitor
6 | 103 | bob | fox | joeschool | 44 | councelor
7 | 104 | tom | fox | joeschool | 41 | janitor

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-13 : 19:04:16

You can just group by the items to get a distinct list. Do you need to return the ID or rowcount columns?
SELECT firstname,lastname,schoolname,schoolid,positionname
FROM v_employee_active
GROUP BY rowcount,firstname,lastname,schoolname,schoolid,positionname

You can get the highest rowcount or ID by using max

SELECT max(ID) as MaxID,firstname,lastname,schoolname,schoolid,positionname
FROM v_employee_active
GROUP BY rowcount,firstname,lastname,schoolname,schoolid,positionname

Poor planning on your part does not constitute an emergency on my part.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2011-01-13 : 19:34:56

yes i want to return all columns of data that i have.

I was hoping it would group all the records by ID, look at all the rowcount column numbers, and give me the record that has the highest rowcount.

so if i had this data below, you can see that my ID has 4 of the same ID's, it should see all of these, then out of those give me the record with the highest ID

1 | 100 | joe | fox | joeschool | 45 | janitor
2 | 100 | joe | fox | joeschool | 45 | teacher
3 | 100 | joe | fox | joeschool | 45 | janitor2
4 | 100 | joe | fox | joeschool | 45 | teachers

Result
4 | 100 | joe | fox | joeschool | 45 | teachers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-13 : 19:38:55

Here is one way using the ROW_NUMBER() ranking function:

SELECT
    [rowcount],
    id,
    firstname,
    lastname,
    schoolname,
    schoolid,
    positionname
FROM
    (    
        SELECT
            [rowcount],
            id,
            firstname,
            lastname,
            schoolname,
            schoolid,
            positionname,
            ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [rowcont] DESC) AS RowNum

        FROM
            v_employee_active

    ) AS T
WHERE
    RowNum = 1

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2011-01-13 : 21:01:15

Lamprey

Will that group all the matching ID's together and then pick the largest one, and display that record?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 00:27:26

@@jjmusic

If the columns in your table has exactly same names as you mentioned above then just replace the table name in below query and check the output! .. hope this helps!

Select MaxRowCnt as [RowCount], A.id,firstname,lastname,schoolname,schoolid,positionname
from table1 A
Inner Join (
SElect id,MAX([rowCount]) MaxRowCnt from table1 Group by id having COUNT([rowCount])>1
)B on A.[rowcount]=B.MaxRowCnt

Union All
Select *
from table1 where id not in (SElect id from table1 Group by id having COUNT([rowCount])>1)

Cheers!~
MIK

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2011-01-14 : 10:00:54

Yes, the first column is called rowcount, and the second id. the ID columns is the one i want to match on, and then out of those give me the record with the hightest rowcount

1 | 100 | joe | fox | joeschool | 45 | janitor
2 | 100 | joe | fox | joeschool | 45 | teacher
3 | 100 | joe | fox | joeschool | 45 | janitor2
4 | 100 | joe | fox | joeschool | 45 | teachers

Result
4 | 100 | joe | fox | joeschool | 45 | teachers

* Since 4 records match 100 for the ID column, out of those 4 records i want to only display the one with the highest rowcount column.

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2011-01-14 : 11:31:24

@@ MIK_2008

I tried to run just

Select MaxRowCnt as [RowCount], A.id,firstname,lastname,schoolname,schoolname,schoolposition
from test_table A
Inner Join (
Select id,MAX([rowcount]) MaxRowCnt from test_table Group by id having COUNT([rowCount])>1
)B on A.[rowcount]=B.MaxRowCnt

it it brings back rows that are correct, but dosent show rows that only have 1 record.
Some records might only have 1 record, and others might have 3 or 4... so it looks like yours works half way, and only matches people who have 2 or more records..

Select MaxRowCnt as [RowCount], A.id,firstname,lastname,schoolname,schoolname,schoolposition
from test_table A
Inner Join (
Select id,MAX([rowcount]) MaxRowCnt from test_table Group by id having COUNT([rowCount])>1
)B on A.[rowcount]=B.MaxRowCnt

When i run your full code i get the error below.
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 13:51:23

well you have to run full code as one of its section is listing all the duplicate rows once. and the later section returns all rows that are just once... It seems that your original table have more columns than as you mentioned in your original post .. Just in case if your table has more rows then select only same number of rows as it is selected in the upper part of my query .. you will get the desired result.... I could just give you an idea as how you can get the desired result .. in order to cope with that error select same number of columns in both parts/sections: above and Below the union...

Cheers!
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-14 : 13:56:53

If you want to use Miks method just remove the HAVING clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources