April 22, 2013 at 6:41 pm
I HAVE DATA LIKE THIS
ID DATE PID
691935 2012-05-11 15:32:09.377 00071015523
691935 2012-05-11 00:00:00.000 00093063801
691935 2012-05-11 15:34:37.147 00093103993
691935 2012-09-19 11:27:55.420 00093715310
691935 2012-11-16 15:28:21.843 00093715410
691935 2013-03-08 15:19:53.013 00093720210
691935 2013-03-08 15:19:22.867 00093721401
691935 2012-07-13 00:00:00.000 00247035330
691935 2012-07-13 15:53:21.343 00247035430
691935 2013-03-14 13:50:01.803 00247181304
691935 2013-03-14 00:00:00.000 00247196500
691935 2012-10-12 00:00:00.000 00456132100
691935 2012-05-11 15:32:36.580 51079099720
691935 2012-05-11 15:31:38.957 53489046910
691935 2012-10-12 13:51:39.530 63739013701
HOW I CAN use this logic here in the above data
I ID,PID of MAX(DATE)
The end result should be
ID DATE PID
691935 2013-03-14 13:50:01.803 00247181304
Here is my code
Selct DISTINCT
ID,MAX(DATE) MAX_DATE,PID
FROM Mytable
Group by ID,PID
I am not getting what I want. Any help would be great appreciate.
April 22, 2013 at 8:03 pm
You need to get the latest date separately from the main query. For example:
-- Set up test data
set language english;
declare @MyTable table (
[ID] int,
[DATE] datetime,
[PID] char(11)
);
insert into @MyTable values ('691935', '2012-05-11 15:32:09.377', '00071015523');
insert into @MyTable values ('691935', '2012-05-11 00:00:00.000', '00093063801');
insert into @MyTable values ('691935', '2012-05-11 15:34:37.147', '00093103993');
insert into @MyTable values ('691935', '2012-09-19 11:27:55.420', '00093715310');
insert into @MyTable values ('691935', '2012-11-16 15:28:21.843', '00093715410');
insert into @MyTable values ('691935', '2013-03-08 15:19:53.013', '00093720210');
insert into @MyTable values ('691935', '2013-03-08 15:19:22.867', '00093721401');
insert into @MyTable values ('691935', '2012-07-13 00:00:00.000', '00247035330');
insert into @MyTable values ('691935', '2012-07-13 15:53:21.343', '00247035430');
insert into @MyTable values ('691935', '2013-03-14 13:50:01.803', '00247181304');
insert into @MyTable values ('691935', '2013-03-14 00:00:00.000', '00247196500');
insert into @MyTable values ('691935', '2012-10-12 00:00:00.000', '00456132100');
insert into @MyTable values ('691935', '2012-05-11 15:32:36.580', '51079099720');
insert into @MyTable values ('691935', '2012-05-11 15:31:38.957', '53489046910');
insert into @MyTable values ('691935', '2012-10-12 13:51:39.530', '63739013701');
-- Query results
with LatestDate as (
select max([DATE]) as [Date] from @MyTable
)
select m.[ID], m.[DATE], m.[PID]
from @MyTable m
join LatestDate d on d.[DATE] = m.[DATE];
April 22, 2013 at 10:48 pm
Thank You for your reply. I really appreciate your sample data.
Your query is good for sample data. However I have 3m rows in my database and i want, IF are same i want MAX(DATE). SO at the end My ID should be unique.
Please guide.
Thank You.
April 22, 2013 at 11:03 pm
April 25, 2013 at 12:10 am
rocky_498 (4/22/2013)
Your query is good for sample data. However I have 3m rows in my database and i want, IF are same i want MAX(DATE). SO at the end My ID should be unique.Please guide.
Thank You.
You might want to try this:
-- Set up test data
set language english;
declare @MyTable table (
[ID] int,
[DATE] datetime,
[PID] char(11)
);
insert into @MyTable values ('691935', '2012-05-11 15:32:09.377', '00071015523');
insert into @MyTable values ('691935', '2012-05-11 00:00:00.000', '00093063801');
insert into @MyTable values ('691935', '2012-05-11 15:34:37.147', '00093103993');
insert into @MyTable values ('691935', '2012-09-19 11:27:55.420', '00093715310');
insert into @MyTable values ('691935', '2012-11-16 15:28:21.843', '00093715410');
insert into @MyTable values ('691935', '2013-03-08 15:19:53.013', '00093720210');
insert into @MyTable values ('691935', '2013-03-08 15:19:22.867', '00093721401');
insert into @MyTable values ('691935', '2012-07-13 00:00:00.000', '00247035330');
insert into @MyTable values ('691935', '2012-07-13 15:53:21.343', '00247035430');
insert into @MyTable values ('691935', '2013-03-14 13:50:01.803', '00247181304');
insert into @MyTable values ('691935', '2013-03-14 00:00:00.000', '00247196500');
insert into @MyTable values ('691935', '2012-10-12 00:00:00.000', '00456132100');
insert into @MyTable values ('691935', '2012-05-11 15:32:36.580', '51079099720');
insert into @MyTable values ('691935', '2012-05-11 15:31:38.957', '53489046910');
insert into @MyTable values ('691935', '2012-10-12 13:51:39.530', '63739013701');
SELECT [ID], [DATE], [PID]
FROM
(
SELECT [ID], [DATE], [PID]
,rn=ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [DATE] DESC)
FROM @MyTable
) a
WHERE rn=1
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply