SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Max for duplicates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfm
Posting Yak Master

145 Posts

Posted - 07/30/2013 :  11:49:05  Show Profile  Reply with Quote
Hi there,

I got many duplicates in T_1

I have Name_col and Date_col.

Each Name_col has several different dates and I need the Max date of each name_row. So i will keep the name of my client and the last date instead of the name plus the historical dates.

Any tips?

Thanks!!

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1754 Posts

Posted - 07/30/2013 :  12:11:22  Show Profile  Reply with Quote
select Name_col, max(Date_col) as maxDate
from MyTable
group by Name_col


=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 07/30/2013 :  13:11:55  Show Profile  Reply with Quote
Perfect.

Thanks a lot.

I have another question: how im able to subtract one month from a date and create a new column?

Imagine i have 2013-07-18 and I need to use a query in SQL that gives me 2013-06-18


Thank you!!

quote:
Originally posted by Bustaz Kool

select Name_col, max(Date_col) as maxDate
from MyTable
group by Name_col


=================================================
The cure for anything is salt water -- sweat, tears, or the sea. -Isak Dinesen

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/30/2013 :  13:32:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can use the DATEADD function for that.

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

Subscribe to my blog
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 07/30/2013 :  14:02:29  Show Profile  Reply with Quote
Hello

I have tried but I cannot reach the solution.

Could you type a query?

Thank you


quote:
Originally posted by tkizer

You can use the DATEADD function for that.

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

Subscribe to my blog

Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 07/30/2013 :  14:06:00  Show Profile  Reply with Quote
The matter is that I need to subtract one month in millions of dates, is not just one.

For one date, I can do the query, but it needs to process all the dates using one query

Any tip?

Thanks



quote:
Originally posted by tkizer

You can use the DATEADD function for that.

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

Subscribe to my blog

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/30/2013 :  14:15:49  Show Profile  Visit tkizer's Homepage  Reply with Quote
There is no issue with using DATADD with millions of dates. Just past it the column name.

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

Subscribe to my blog
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 07/30/2013 :  14:33:59  Show Profile  Reply with Quote
Select col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, dateadd(month, coldate, -1)
into new_table
from My_table
group by col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

What im doing wrong?

Thank you



quote:
Originally posted by tkizer

There is no issue with using DATADD with millions of dates. Just past it the column name.

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

Subscribe to my blog

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37157 Posts

Posted - 07/30/2013 :  14:36:31  Show Profile  Visit tkizer's Homepage  Reply with Quote
You don't have the parameters in the correct order: http://msdn.microsoft.com/en-us/library/ms186819.aspx

dateadd(month, -1, coldate) as NewColumnName

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

Subscribe to my blog
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 07/31/2013 :  09:01:20  Show Profile  Reply with Quote
Thank you



quote:
Originally posted by tkizer

You don't have the parameters in the correct order: http://msdn.microsoft.com/en-us/library/ms186819.aspx

dateadd(month, -1, coldate) as NewColumnName

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

Subscribe to my blog

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000