getting multiple records when using max

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)

getting multiple records when using max

Author

Topic

wendymackay
Starting Member

10 Posts

Posted - 2008-09-17 : 15:46:15

I have a query that almost works except I have duplicates that I'm trying to remove based on a date field. I need to have the latest record which I beleive should be Max(date)

My current query looks like this:
select b.DATE, a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR
from employee a, titles b
where (a.DEPTID = '2410' or a.DEPTID = '5565' or a.DEPTID = '5566' or a.DEPTID = '5563') and
a.ID = b.ID and
(a.EMPL_STATUS = 'A' or a.EMPL_STATUS = 'P' or
a.EMPL_STATUS = 'S') and
convert(numeric,a.GRADE) >= 12 and
convert(numeric,a.GRADE) <= 15

But the outcome looks something like this:
DATE ID Name ELIG_DT Service_DT Birthdate Descr
===========================================================
09/01/08 2 Tom Brown 08/26/10 08/08/08 04/03/64 Manager
07/14/07 2 Tom Brown 08/26/10 08/08/08 04/03/64 Manager
06/11/08 5 Sue Smith 06/31/11 07/10/07 06/11/45 Sr Manager
04/11/07 9 Carol Gold 09/11/09 05/22/07 05/04/60 Assistant
02/03/07 9 Carol Gold 09/11/09 05/22/07 05/04/60 Assistant

and I need to have only one Tom Brown and one Carol Gold with the most recent date.

When I insert Max(b.Date) into the query above my records multiply from 23 to 247,930.

Thanks for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-17 : 15:47:01

You need to add a GROUP BY so that you aggregate the groups.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

wendymackay
Starting Member

10 Posts

Posted - 2008-09-17 : 16:25:54

I thought so too and tried that before but it bogged the query down and produced 287700 rows (each table has over 4000 records).
It should go from 23 down to 18 or so records

Here's what the query looks like:

select Max(b.DATE), a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR
from employee a, titles b
where (a.DEPTID = '2410' or a.DEPTID = '5565' or a.DEPTID = '5566' or a.DEPTID = '5563') and
a.ID = b.ID and
(a.EMPL_STATUS = 'A' or a.EMPL_STATUS = 'P' or
a.EMPL_STATUS = 'S') and
convert(numeric,a.GRADE) >= 12 and
convert(numeric,a.GRADE) <= 15
group by b.ID

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-17 : 17:14:19

try this out:
select Max(b.DATE), a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR
from employee a, titles b
where (a.DEPTID = '2410' or a.DEPTID = '5565' or a.DEPTID = '5566' or a.DEPTID = '5563') and
a.ID = b.ID and
(a.EMPL_STATUS = 'A' or a.EMPL_STATUS = 'P' or
a.EMPL_STATUS = 'S') and
convert(numeric,a.GRADE) >= 12 and
convert(numeric,a.GRADE) <= 15
group by b.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR

hey

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 08:18:59

I'm back to the original 23 records which is better but I still have the duplicates for Tom Brown and Carol Gold. It still isn't selecting the max date.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 08:28:04

quote:
Originally posted by wendymackay

I'm back to the original 23 records which is better but I still have the duplicates for Tom Brown and Carol Gold. It still isn't selecting the max date.

select b.DATE, a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR 
from employee a
join titles b
on a.ID = b.ID 
join (select ID,MAX(DATE) AS maxdate
from titles
group by ID)c
on c.ID=b.ID
and c.maxdate=b.DATEwhere (a.DEPTID IN( '2410' ,'5565', '5566', '5563') and 
and
(a.EMPL_STATUS IN( 'A' , 'P' , 'S') and
convert(numeric,a.GRADE) >= 12 and
convert(numeric,a.GRADE) <= 15

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 09:05:50

I'm getting a syntax error near ')' line 7 which is on the line that reads "group by ID)c".

I've tried rearranging things, removing the paranthesis and putting them other places and the query still doesn't run. I did notice the above had duplicate "and"s on lines 9 and 10 but the query doesn't seem to make it down that far.

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 10:08:14

there were couple of unwanted braces. should be ok now

select b.DATE, a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR 
from employee a
join titles b
on a.ID = b.ID 
join (select ID,MAX(DATE) AS maxdate
from titles
group by ID)c
on c.ID=b.ID
and c.maxdate=b.DATE
where a.DEPTID IN( '2410' ,'5565', '5566', '5563') 
and a.EMPL_STATUS IN( 'A' , 'P' , 'S') and
convert(numeric,a.GRADE) >= 12 and
convert(numeric,a.GRADE) <= 15

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 11:16:40

it bombs at the second join with the same error message. I've backed out and deleted the bottom portion (removing from the words "where" on and it stops at lline 7 with Incorrect syntax near ')'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 11:44:08

quote:
Originally posted by wendymackay

it bombs at the second join with the same error message. I've backed out and deleted the bottom portion (removing from the words "where" on and it stops at lline 7 with Incorrect syntax near ')'

try giving precision and scale for numeric value. if it still errors,
show your query please

select b.DATE, 
a.ID, 
a.NAME, 
a.ELIG_DT, 
a.SERVICE_DT, 
a.BIRTHDATE, 
b.DESCR 
from employee a
join titles b
on a.ID = b.ID 
join 
(select ID,MAX(DATE) AS maxdate
from titles
group by ID)c
on c.ID=b.ID
and c.maxdate=b.DATE
where a.DEPTID IN( '2410' ,'5565', '5566', '5563') 
and a.EMPL_STATUS IN( 'A' , 'P' , 'S') and
convert(numeric(p,s),a.GRADE) >= 12 and
convert(numeric(p,s),a.GRADE) <= 15

i cant understand why you're converting GRADE to numeric though

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 13:37:35

Still bombs. I'm connecting to a sybase database - does that make any difference? Here's the query

select b.DATE, a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR
from employees a
join titles b
on a.ID = b.ID
join (select ID, MAX(DATE) AS maxdate
from titles
group by ID) c
on c.ID = b.ID
and c.maxdate = b.DATE
where a.DEPTID IN( '2410' , '5565' , '5566' , '5563')
and a.EMPL_STATUS IN( 'A' , 'P' , 'S' ) and
convert(numeric(p,s),a.GRADE) >= 12 and
convert(numeric(p,s),a.GRADE) <= 15

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-18 : 13:39:28

quote:
Originally posted by wendymackay

Still bombs. I'm connecting to a sybase database - does that make any difference? Here's the query

This is a Microsoft SQL Server site. Our solutions will work in T-SQL, but may or may not work on other platforms. You should post your question on a site that deals with Sybase to get better answers. I believe there's one over at dbforums.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:40:24

quote:
Originally posted by wendymackay

Still bombs. I'm connecting to a sybase database - does that make any difference? Here's the query

select b.DATE, a.ID, a.NAME, a.ELIG_DT, a.SERVICE_DT, a.BIRTHDATE, b.DESCR
from employees a
join titles b
on a.ID = b.ID
join (select ID, MAX(DATE) AS maxdate
from titles
group by ID) c
on c.ID = b.ID
and c.maxdate = b.DATE
where a.DEPTID IN( '2410' , '5565' , '5566' , '5563')
and a.EMPL_STATUS IN( 'A' , 'P' , 'S' ) and
convert(numeric(p,s),a.GRADE) >= 12 and
convert(numeric(p,s),a.GRADE) <= 15

aha...i see now
this is ms sql server forum and solutions given are as per syntax of sql server. post in some sybase forums if you want sybase supported syntax

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 13:41:26

Oh and Grade is a character field. Before all the date duplicates in table two - titles, the query was written this way to select out individuals who fell amongs these grade ranges. That part I just copy/pasted assuming I needed it.

wendymackay
Starting Member

10 Posts

Posted - 2008-09-18 : 13:43:03

OH - So sorry - No wonder the query syntax isn't working. My bad - thank you though for all your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-18 : 13:46:08

quote:
Originally posted by wendymackay

OH - So sorry - No wonder the query syntax isn't working. My bad - thank you though for all your help.

No worries

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources