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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Distinct Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tooba
Posting Yak Master

143 Posts

Posted - 04/22/2013 :  20:40:29  Show Profile  Reply with Quote
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.

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 04/22/2013 :  21:13:51  Show Profile  Reply with Quote
select top (1)
	id,
	date,
	pid
from
	YourTable
order by
	date desc;
If there happen to be more than one row with the same max date, and if you want to pick a specific one out of that, add more conditions in the order by clause.

Alternatively, if you want to get all the rows that have the max date, instead of "TOP (1)" use "TOP (1) WITH TIES"
Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 04/22/2013 :  21:32:56  Show Profile  Reply with Quote
James,

Thank you for your reply. I am sorry i don't understand. When i should use your syntax
select top (1)
id,
date,
pid
from
YourTable
order by
date desc;

Note:- That was just a sample data that i use just for e.g.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 04/22/2013 :  21:37:47  Show Profile  Reply with Quote
Did you mean that you have several groups of ID's, and PID's, and in each case you want to pick the one with the latest timestamp? If so, can you try this?
select Id, date, pid from
(
	select *,
		row_number() over (partition by ID, PID order by date desc) as RN
	from
		MyTable
) s where RN = 1;
If you want to get ties, use RANK() instead of ROW_NUMBER().
Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 04/22/2013 :  22:04:00  Show Profile  Reply with Quote
James, Once again that you for your help.

I am not getting what i need.

Here is the situation,
I have ID,PID AND DATE. I want to Pick Max (Date)
E.g
ID,PID,DATE
1,123,1/1/2013
2,123,2/4/2013

Result should be
ID,PID,DATE
2,123,2/4/2013

select Id, MAX(date), pid from
(
select *,
row_number() over (partition by ID, PID order by date desc) as RN
from
MyTable
) s where RN = 1
group by ID,pid

Please let me Thanks.
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/22/2013 :  22:24:51  Show Profile  Reply with Quote
You should be able to use the first solution James K recommended ie:

select top (1)
	id,
	date,
	pid
from
	YourTable
order by
	date desc;

Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 04/23/2013 :  00:03:00  Show Profile  Reply with Quote
It was just a sample data. How i can use this solution.
In this solution i can get only one row (top 1).
I want to use this query for a whole table...
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 04/23/2013 :  00:25:01  Show Profile  Reply with Quote
Can you provide more details; such as your table description, data and expected output from this query.
we can help you better if you can provide us more details.
Go to Top of Page

tooba
Posting Yak Master

143 Posts

Posted - 04/23/2013 :  00:39:45  Show Profile  Reply with Quote
Create table MyTable (
[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');
insert into MyTable values ('691934', '2012-10-12 13:51:39.530', '63739013701');
insert into MyTable values ('691935', '2013-03-12 13:51:39.530', '63739013701');














select * from MyTable

SELECT
ID,
MAX(DATE) DOS
FROM MyTable
GROUP BY ID
-- Works fine and i am getting max DOS

SELECT
ID,
MAX(DATE) DOS,
PID
FROM MyTable
GROUP BY ID,PID
--Same e.g and i want ID,PID WITH MAX DOS. what should i do?
-- NOTE:- I have 37m rows in the table, I am looking ID,PID WITH MAX DOS.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 04/23/2013 :  00:50:41  Show Profile  Reply with Quote
-- 1.
SELECT t1.ID, DOS, PID
FROM MyTable t1 
JOIN (SELECT ID, MAX(DATE) DOS FROM MyTable GROUP BY ID) t2
ON t1.ID = t2.ID AND t1.DATE = t2.DOS

-- 2.
SELECT DISTINCT
	ID,
	MAX(DATE) OVER(PARTITION BY ID) DOS,
	PID
FROM MyTable


--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3565 Posts

Posted - 04/23/2013 :  08:26:34  Show Profile  Reply with Quote
quote:
Originally posted by tooba

James, Once again that you for your help.

I am not getting what i need.

Here is the situation,
I have ID,PID AND DATE. I want to Pick Max (Date)
E.g
ID,PID,DATE
1,123,1/1/2013
2,123,2/4/2013

Result should be
ID,PID,DATE
2,123,2/4/2013

select Id, MAX(date) date, pid from
(
select *,
row_number() over (partition by ID, PID order by date desc) as RN
from
MyTable
) s where RN = 1
group by ID,pid

Please let me Thanks.

tooba, you added a max function and group by to the code I posted. You don't need those.
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