December 14, 2007 at 1:24 pm
I've been trying to get this query to work all afternoon.
I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.
there are 2 things that I need
1. make the records unique like select distinct A, B
2. I need to bring the datetime C with the record that has the most current date.
If I run
select distinct A, B from table
this gives me the right records, field c is eliminated
If I run
select distinct A, B, C
rows show that have different dates in field C. I only need one record with the most current date records
anyone have any thoughts
December 14, 2007 at 1:29 pm
TaffyLewis (12/14/2007)
I've been trying to get this query to work all afternoon.I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.
there are 2 things that I need
1. make the records unique like select distinct A, B
2. I need to bring the datetime C with the record that has the most current date.
try
select A, B, Max(C) as C
from tablename
group by A, B
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 14, 2007 at 1:34 pm
select A, B, MAX(C) AS MaxC
from Yourtbl
group by A,B
December 14, 2007 at 1:50 pm
I think I made a mistake in trying to explain this. I tried to make this simple for an explanation, but I think I need to add that these come from two tables. When I tried the solution, I got errors. These fields come from different tables. Let's try this.
select t1.A, t1.C, t2.C
from t1 inner join t2
on t1.A = t2.A
and t1.C = t2.C
t2.c is the date field
When I try to run the Max on this I generate the following error:
Column 't2.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
sry about trying to make this too simple, but I really could use the help.
December 14, 2007 at 2:07 pm
Here is what would really help, provide us with the DDL for the tables, some sample data for the tables, and your expected results based on the sample data.
Jeff Moden worte an excellent article on how to ask for help that will give you a good idea of what we could use to help us help you.
😎
December 14, 2007 at 2:48 pm
TaffyLewis (12/14/2007)
select t1.A, t1.C, t2.C
from t1 inner join t2
on t1.A = t2.A
and t1.C = t2.C
t2.c is the date field
t1.C and t2.C are the same values. Your join assures that. So, this should be what you need. Why return 2 columns that are the same?
select t1.A, MAX(t2.C)
from t1 inner join t2
on t1.A = t2.A
and t1.C = t2.C
GROUP BY t1.A
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 14, 2007 at 2:52 pm
TaffyLewis (12/14/2007)
select t1.A, t1.C, t2.C
from t1 inner join t2
on t1.A = t2.A
and t1.C = t2.C
t2.c is the date field
When I try to run the Max on this I generate the following error:
Column 't2.C' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
IN that case try :
select t1.A, t1.C, MAX(t2.C) as T2C
from t1 inner join t2
on t1.A = t2.A
and t1.C = t2.C
group by t1.A, T1.C
from what I see your problem is exactly what the interpreter says, you just need that group by clause in there.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 17, 2007 at 6:01 am
ok, I'll take a look at that and create the quesiton better.
For anyone who might read this and have not read the article, here it is:
Forum Etiquette: How to post data/code on a forum to get the best help
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 17, 2007 at 7:34 am
Here it is better asked!
If OBJECT_ID('TestA','U') is not null
drop table TestA
CREATE TABLE [TestA]
(
[comp_id] [char](5) NOT NULL,
[applcnt_id] [varchar](16) NULL
)
If OBJECT_ID('TestB','U') is not null
drop table TestB
CREATE TABLE [TestB]
(
[comp_Id] [char](5) NOT NULL,
[applcnt_id] [varchar](16) NOT NULL,
[needed_dt] [datetime] NULL
)
INSERT INTO TestA
(comp_id, applcnt_id)
SELECT 'MMMM','MMMM000000000015' UNION ALL
select 'MMMM','MMMM000000000025'
INSERT INTO TestB
(comp_id, applcnt_id,needed_dt)
SELECT 'MMMM','MMMM000000000015','2007-11-15 00:00:00.000' UNION ALL
select 'MMMM','MMMM000000000025','2007-11-15 00:00:00.000' UNION ALL
select 'MMMM','MMMM000000000025','2007-11-14 00:00:00.000'
--basic query that gives 3 rows
select TestA.comp_id, TestA.applcnt_id, TestB.needed_dt
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
--if I use distinct I get two rows
select distinct TestA.comp_id, TestA.applcnt_id
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
--I would like to get the two rows, but with one datetime field
-- and with the current datetime
--when I try max and group by I get errors
select TestA.comp_id, TestA.applcnt_id, Max(TestB.needed_dt) As NeededMax
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
TaffyLewis (12/14/2007)
I've been trying to get this query to work all afternoon.I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.
there are 2 things that I need
1. make the records unique like select distinct A, B
2. I need to bring the datetime C with the record that has the most current date.
If I run
select distinct A, B from table
this gives me the right records, field c is eliminated
If I run
select distinct A, B, C
rows show that have different dates in field C. I only need one record with the most current date records
anyone have any thoughts
December 17, 2007 at 7:45 am
I figured it out. Going through the steps and testing to make sure I got it right showed me that I used the group by incorrectly.
Thanks, Lynn, for making me read the article and go through the steps. I definately learned from it.
Thanks again!
🙂
TaffyLewis (12/17/2007)
Here it is better asked!If OBJECT_ID('TestA','U') is not null
drop table TestA
CREATE TABLE [TestA]
(
[comp_id] [char](5) NOT NULL,
[applcnt_id] [varchar](16) NULL
)
If OBJECT_ID('TestB','U') is not null
drop table TestB
CREATE TABLE [TestB]
(
[comp_Id] [char](5) NOT NULL,
[applcnt_id] [varchar](16) NOT NULL,
[needed_dt] [datetime] NULL
)
INSERT INTO TestA
(comp_id, applcnt_id)
SELECT 'MMMM','MMMM000000000015' UNION ALL
select 'MMMM','MMMM000000000025'
INSERT INTO TestB
(comp_id, applcnt_id,needed_dt)
SELECT 'MMMM','MMMM000000000015','2007-11-15 00:00:00.000' UNION ALL
select 'MMMM','MMMM000000000025','2007-11-15 00:00:00.000' UNION ALL
select 'MMMM','MMMM000000000025','2007-11-14 00:00:00.000'
--basic query that gives 3 rows
select TestA.comp_id, TestA.applcnt_id, TestB.needed_dt
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
--if I use distinct I get two rows
select distinct TestA.comp_id, TestA.applcnt_id
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
--I would like to get the two rows, but with one datetime field
-- and with the current datetime
--when I try max and group by I get errors
select TestA.comp_id, TestA.applcnt_id, Max(TestB.needed_dt) As NeededMax
From TestA inner join TestB
on TestA.comp_id = TestB.comp_id
and TestA.applcnt_id = TestB.applcnt_id
TaffyLewis (12/14/2007)
I've been trying to get this query to work all afternoon.I have 3 fields A, B and C in a table. Field C is a datetime field. There are mulitple records in the table. I need to bring back one row for each record where field A and B are unique, but I want to grab the most current date for field C.
there are 2 things that I need
1. make the records unique like select distinct A, B
2. I need to bring the datetime C with the record that has the most current date.
If I run
select distinct A, B from table
this gives me the right records, field c is eliminated
If I run
select distinct A, B, C
rows show that have different dates in field C. I only need one record with the most current date records
anyone have any thoughts
December 17, 2007 at 8:30 am
I'm glad to have been of assistance, even though all I did was point you to Jeff's article. Be sure to post a thank you to Jeff as well for writing that article.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply