SQL Question - 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

SQL Question

Author

Topic

mlr0911
Starting Member

2 Posts

Posted - 2011-07-26 : 20:02:56

Is there a way that I can only look at the greater date record in the example below? Right now, it's displaying both results...I only want to see 1 results:

Current Results:
Acct# Date
1235 12-12-2011
1235 10-12-2011

Desired Results:
Acct# Date
1235 12-12-2011

Thank you for your help.

singularity
Posting Yak Master

153 Posts

Posted - 2011-07-26 : 20:13:12

[code]
select acct, max([date]) as [date]
from yourtable
group by acct
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-26 : 20:14:13

select [Acct#], MAX([Date])
from YourTable
group by [Acct#]

Alternatively, you could use ROW_NUMBER() if you need to see more detail. A derived table works too.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-26 : 20:14:34

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

Subscribe to my blog

mlr0911
Starting Member

2 Posts

Posted - 2011-07-26 : 21:20:45

Thanks for the reply...can this work with multiple columns? I have more columns than Acct# and Date...Sorry, I should have specified that earlier. I can't seem to get it to work correctly, it continues to bring in all records where I only want to see the max date of the acct# with the other necessary columns. Here is an example of the SQL that I'm currently running without MAX.

I've tried to rewrite it using MAX and entering GROUP By, but I keep getting an aggregate error when only specifying 1 field..acct#.

Thanks for your help.

SELECT ACCT.ACCT_KEY, btl.pl_user_key, pl.pltype_code, ACCT.status_code, btl.date
From btl inner join ACCT on 
btl.acct_sak = acct.acct_sak inner join
pl on btl.pl_sak = pl.pl_sak
Where Acct.Acct_key in ('12222','2222','3333') and
btl.transtype in ('ABD,'DBG')
Order by Acct.Acct_key, btl.Date DESC

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-26 : 23:31:58

If you need other columns, you'll need to use the ROW_NUMBER() function or a derived table/CTE. I would recommend ROW_NUMBER(). There are many examples of how to use it if you search our forums.

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

Subscribe to my blog

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-07-27 : 03:31:54

As Tara said, you could do this many different ways. Not at my comp with SQL Server right now, but at first glance, you could use a subquery like this with your data:

SELECT ACCT.ACCT_KEY, btl.pl_user_key, pl.pltype_code, ACCT.status_code, btl.date
From btl inner join ACCT on
btl.acct_sak = acct.acct_sak inner join
pl on btl.pl_sak = pl.pl_sak
Where Acct.Acct_key in ('12222','2222','3333') and
btl.transtype in ('ABD,'DBG') and
btl.date in (select max(date)
from btl)

Order by Acct.Acct_key, btl.Date DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources