Group by - 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

Author

Topic

nonsparker
Starting Member

2 Posts

Posted - 2007-07-27 : 14:54:20

I have a few columns that have duplicate entries, I need to group by or return just the record with the unique id. How do i group by just the first column i am selecting. here are some statements and what they return
******************
SELECT DISTINCT name."NAME-ID", name."FIRST-NAME", name."MIDDLE-NAME", name."LAST-NAME", assignment."HPMASN-SUPER-ID", assignment."HPMASN-FIS-YEAR"
FROM PUB."NAME" name, PUB."HPMASN-ASSIGNMENTS" assignment, PUB."NAME" supername, PUB."HAAPRO-PROFILE" profile, PUB."HAAETY-EMP-TYPES" emptype, PUB."HPMPLN-PLAN" plan

WHERE name."NAME-ID" = '8586'
AND name."NAME-ID" = profile."NAME-ID"
AND profile."HAAETY-EMP-TYPE-CODE" = emptype."HAAETY-EMP-TYPE-CODE"
AND profile."NAME-ID" = assignment."NAME-ID"
AND assignment."HPMASN-SUPER-ID" = supername."NAME-ID"
AND assignment."HPMPLN-ID" = plan."HPMPLN-ID"

ORDER BY assignment."HPMASN-FIS-YEAR" DESC

******************
returns
NAME-ID FIRST-NAME MIDDLE-NAME LAST-NAME HPMASN-SUPER-ID HPMASN-FIS-YEAR
8586 MICHAEL JAMES HIBBELN 7863 2007
8586 MICHAEL JAMES HIBBELN 8664 2007
8586 MICHAEL JAMES HIBBELN 7863 2006
8586 MICHAEL JAMES HIBBELN 8664 2006
8586 MICHAEL JAMES HIBBELN 7863 2005
8586 MICHAEL JAMES HIBBELN 8748 2005
8586 MICHAEL JAMES HIBBELN 8748 2004
8586 MICHAEL JAMES HIBBELN 9529 2004
8586 MICHAEL JAMES HIBBELN 7982 2003
8586 MICHAEL JAMES HIBBELN 10607 2002
8586 MICHAEL JAMES HIBBELN 10607 2001
8586 MICHAEL JAMES HIBBELN 10607 2000

i want to return 1 record with the most current HPMASN-FIS-YEAR

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 14:56:03

Use the group by statement, and than in your select statement use the MAX function

nonsparker
Starting Member

2 Posts

Posted - 2007-07-27 : 15:05:10

like this

SELECT name."NAME-ID", name."FIRST-NAME", name."MIDDLE-NAME", name."LAST-NAME", assignment."HPMASN-SUPER-ID", max(assignment."HPMASN-FIS-YEAR")
FROM PUB."NAME" name, PUB."HPMASN-ASSIGNMENTS" assignment, PUB."NAME" supername, PUB."HAAPRO-PROFILE" profile, PUB."HAAETY-EMP-TYPES" emptype, PUB."HPMPLN-PLAN" plan

WHERE name."NAME-ID" = '8586'
AND name."NAME-ID" = profile."NAME-ID"
AND profile."HAAETY-EMP-TYPE-CODE" = emptype."HAAETY-EMP-TYPE-CODE"
AND profile."NAME-ID" = assignment."NAME-ID"
AND assignment."HPMASN-SUPER-ID" = supername."NAME-ID"
AND assignment."HPMPLN-ID" = plan."HPMPLN-ID"
GROUP BY name."NAME-ID", name."FIRST-NAME", name."MIDDLE-NAME", name."LAST-NAME", assignment."HPMASN-SUPER-ID", supername."FIRST-NAME", supername."LAST-NAME"
ORDER BY assignment."HPMASN-FIS-YEAR" DESC

returns

NAME-ID FIRST-NAME MIDDLE-NAME LAST-NAME HPMASN-SUPER-ID max(HPMASN-FIS-YEAR)
8586 MICHAEL JAMES HIBBELN 8664 2007
8586 MICHAEL JAMES HIBBELN 7863 2007
8586 MICHAEL JAMES HIBBELN 8748 2005
8586 MICHAEL JAMES HIBBELN 9529 2004
8586 MICHAEL JAMES HIBBELN 7982 2003
8586 MICHAEL JAMES HIBBELN 10607 2002

jpockets
Starting Member

45 Posts

Posted - 2007-07-27 : 16:06:27

use the max with all the other columns in the select statement

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 16:07:59

Are you using SQL Server 2000 or SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-27 : 16:51:32

[code]SELECT name.[NAME-ID],
name.[FIRST-NAME],
name.[MIDDLE-NAME],
name.[LAST-NAME],
assignment.[HPMASN-SUPER-ID],
assignment.[HPMASN-FIS-YEAR]
FROM PUB.[NAME] AS name
INNER JOIN PUB.[HAAPRO-PROFILE] AS profile ON profile.[NAME-ID] = name.[NAME-ID]
INNER JOIN PUB.[HPMASN-ASSIGNMENTS] AS assignment ON assignment.[NAME-ID] = profile.[NAME-ID]
INNER JOIN PUB.[NAME] AS supername ON supername.[NAME-ID] = assignment.[HPMASN-SUPER-ID]
INNER JOIN PUB.[HAAETY-EMP-TYPES] AS emptype ON emptype.[HAAETY-EMP-TYPE-CODE] = profile.[HAAETY-EMP-TYPE-CODE]
INNER JOIN PUB.[HPMPLN-PLAN] AS plan ON plan.[HPMPLN-ID] = assignment.[HPMPLN-ID]
WHERE EXISTS (
SELECT TOP 1 a.[HPMASN-SUPER-ID],
a.[HPMASN-FIS-YEAR]
FROM PUB.[HPMASN-ASSIGNMENTS] AS a
WHERE a.[NAME-ID] = assignment.[NAME-ID]
AND a.[HPMASN-SUPER-ID] = assignment.[HPMASN-SUPER-ID]
AND a.[HPMASN-FIS-YEAR] = assignment.[HPMASN-FIS-YEAR]
ORDER BY a.[HPMASN-FIS-YEAR] DESC,
a.[HPMASN-SUPER-ID] DESC
)
--WHERE name.[NAME-ID] = '8586'
ORDER BY name.[NAME-ID][/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources