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.
Author |
Topic |
dipesh.ccsu
Starting Member
7 Posts |
Posted - 2013-03-26 : 15:06:42
|
Acct# Act Dt number123 6/21/2012 2123 6/21/2012 3123 6/15/2011 5I am trying to get the MAX "ACT DT" for thousands of accounts. Above IS JUST AN EXAMPLE FOR ONE ACCOUNT. So, I used the max function for the act dt and above is the data it return but as one can see the max function did not work. Could anyone suggest how I can get the max date for an account but also if the account has two or more max date I would want to retrieve that as well? |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-26 : 15:18:04
|
Why do you say it did not work? What is the query you are using? What is the data type of ACT DT column? In the example that you showed, there are two rows with the same date. Do you want to get both those rows for Acct# 123, or just one of those rows? If only one which one do you want to get? |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-03-26 : 15:21:23
|
The MAX function works fine. Your query is bad.Should show us what you've tried so far. Here's one way to do what you're after.SELECT t.[Acct#], t.[Act Dt], t.[number]FROM yourTable tJOIN ( SELECT [Acct#], max([Act Dt]) [Act Dt] FROM yourTable GROUP BY [Acct#]) xOn x.[Acct#] = t.[Acct#]And x.[Act Dt] = t.[Act Dt] |
|
|
dipesh.ccsu
Starting Member
7 Posts |
Posted - 2013-03-26 : 16:29:30
|
quote: Originally posted by James K Why do you say it did not work? What is the query you are using? What is the data type of ACT DT column? In the example that you showed, there are two rows with the same date. Do you want to get both those rows for Acct# 123, or just one of those rows? If only one which one do you want to get?
Hi Jame,yes i am trying to retrieve first two rows for that example. below is my sql script.CREATE TABLE OCAM_LINKINGS_UPDATES AS (SELECT V.Acct_Bill_Sys, V.Acct_Bill_Subs, V.Acct_Uniq, V.Acct_Dim_, V.Cust_No, CAST(NULL AS VARCHAR (10)) AS New_Cust_#, CAST(NULL AS VARCHAR (10)) AS Cust_Compare, V.Acct_Stat_Cd, V.Acct_Cust_Type_Cd, V.Acct_Cust_End_Dt, MAX(V.Acct_Activation_Dt) AS ACT_DT FROM eACCT_HIST_V V,CUST AWHERE V.Acct_Uniq_Cust_No = A.Acct_Uniq_Cust_NoGROUP BY 1,2,3,4,5,6,7,8,9,10)WITH DATA PRIMARY INDEX (Acct_Uniq,Acct_Dim_Start) |
|
|
dipesh.ccsu
Starting Member
7 Posts |
Posted - 2013-03-26 : 16:31:01
|
Thanks for your input i posted what i have done so far.quote: Originally posted by russell The MAX function works fine. Your query is bad.Should show us what you've tried so far. Here's one way to do what you're after.SELECT t.[Acct#], t.[Act Dt], t.[number]FROM yourTable tJOIN ( SELECT [Acct#], max([Act Dt]) [Act Dt] FROM yourTable GROUP BY [Acct#]) xOn x.[Acct#] = t.[Acct#]And x.[Act Dt] = t.[Act Dt]
|
|
|
dipesh.ccsu
Starting Member
7 Posts |
Posted - 2013-03-26 : 16:31:01
|
Thanks for your input i posted what i have done so far.quote: Originally posted by russell The MAX function works fine. Your query is bad.Should show us what you've tried so far. Here's one way to do what you're after.SELECT t.[Acct#], t.[Act Dt], t.[number]FROM yourTable tJOIN ( SELECT [Acct#], max([Act Dt]) [Act Dt] FROM yourTable GROUP BY [Acct#]) xOn x.[Acct#] = t.[Acct#]And x.[Act Dt] = t.[Act Dt]
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-03-26 : 17:29:23
|
Where did you get syntax like that? What RDBMS are you using?The query I gave you works, you can modify it for your column/table names.If you want to create a table in MS SQL Server with the output of a SELECT statement, then the syntax isSELECT col1, col2, col3...etcINTO NewTableFROM TablesWHERE etc. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-03-26 : 17:43:10
|
This is Microsoft SQL Server code, so I don't know if this will help, but here's another way to do it in t-sqlDECLARE @Table TABLE(AcctNum tinyint,ActDt date,Number tinyint)INSERT INTO @TableVALUES (123,'6/21/2012',2),(123,'6/21/2012', 3),(123,'6/15/2011',5)SELECT t1.AcctNum,t1.ActDt,t1.NumberFROM(select t.AcctNum,t.ActDt,t.Number ,RANK() over(partition by t.AcctNum order by Actdt DESC)as rnk from @Table t ) t1WHERE t1.rnk = 1JimEveryday I learn something that somebody else already knew |
|
|
|
|
|
|
|