June 8, 2012 at 2:27 pm
Lets say I have a dataset from a join of two tables:
Table A one has business ID, Name
Table B two has customer name, date
Table A Data
ID BNAME
1 Nasdaq
2 Apple
Table B Data
Customer Date ID
Jerry 1/2/2012 1
James 4/2/2012 1
Harry 3/5/2012 2
Karl 3/2/2012 2
So lets say I run the query and join on the ID to get a dataset like this:
BNAME CUSTOMER DATE
Nasdaq Jerry 1/2/2012
Nasdaq James 4/2/2012
Apple Harry 3/5/2012
Apple Karl 3/2/2012
What if I wanted the date field to only display the max for its group? So that James and Jerry's date was 4/2/2012, and Harry and Karl's are 3/5/2012?
BNAME CUSTOMER DATE
Nasdaq Jerry 4/2/2012
Nasdaq James 4/2/2012
Apple Harry 3/5/2012
Apple Karl 3/5/2012
I can figure out how to select the max date from a joined group, but populating all rows with the max of each ID I'm stumped on.
June 8, 2012 at 2:42 pm
First, please pay close attention to how I created the tables and sample data. This is what you should do when asking questions instead of relying on us to do it for you.
CREATE TABLE dbo.TableA (
ID INT,
BName VARCHAR(30)
);
CREATE TABLE dbo.TableB (
Customer varchar(30),
CDate DATETIME,
ID INT
);
INSERT INTO dbo.TableA
SELECT 1, 'Nasdaq' UNION ALL
SELECT 2, 'Apple';
INSERT INTO dbo.TableB
SELECT 'Jerry','20120102', 1 UNION ALL
SELECT 'James','20120204', 1 UNION ALL
SELECT 'Harry','20120503', 2 UNION ALL
SELECT 'Karl','20120203', 2;
SELECT
BName,
Customer,
MAX(CDate) OVER (PARTITION BY a.ID) CDate
FROM
dbo.TableA a
INNER JOIN dbo.TableB b
ON (a.ID = b.ID);
DROP TABLE dbo.TableA;
DROP TABLE dbo.TableB;
June 8, 2012 at 2:47 pm
How about:
create table #tablea (
ta_id int not null,
bname varchar(20) not null)
create table #tableb (
customer varchar(20) not null,
c_date date not null,
ta_id int not null)
insert #tablea ( ta_id, bname) values (1,'Nasdaq')
insert #tablea ( ta_id, bname) values (2,'Apple')
insert #tableb (customer,c_date,ta_id) values ('Jerry','20120201',1)
insert #tableb (customer,c_date,ta_id) values ('James','20120204',1)
insert #tableb (customer,c_date,ta_id) values ('Harry','20120305',2)
insert #tableb (customer,c_date,ta_id) values ('Karl','20120302',2)
select a.bname, b.customer, c.m_date
from #tablea a join #tableb b on a.ta_id = b.ta_id
join (select ta_id, MAX(c_date) as m_date from #tableb group by ta_id) c
on a.ta_id = c.ta_id
June 8, 2012 at 2:49 pm
AKKKKK!!! Too slow, and Lynn's solution is better.
June 8, 2012 at 2:52 pm
Lynn,
That worked perfectly. Hours spent trying to do this to have you solve it in seconds. I guess that is why I am posting in the newbie section!
I will create the temp tables in the future.
Thanks for the help.
June 8, 2012 at 3:08 pm
Glad to be able to help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply