Distinct Query Help - SQL Server Forums

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.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Distinct Query Help

Author

Topic

tooba
Posting Yak Master

224 Posts

Posted - 2013-04-22 : 20:40:29

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 21:13:51

[code]select top (1)
id,
date,
pid
from
YourTable
order by
date desc;[/code]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"

tooba
Posting Yak Master

224 Posts

Posted - 2013-04-22 : 21:32:56

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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-22 : 21:37:47

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().

tooba
Posting Yak Master

224 Posts

Posted - 2013-04-22 : 22:04:00

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.

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-22 : 22:24:51

You should be able to use the first solution James K recommended ie:
[CODE]
select top (1)
id,
date,
pid
from
YourTable
order by
date desc;

[/CODE]

tooba
Posting Yak Master

224 Posts

Posted - 2013-04-23 : 00:03:00

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...

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-04-23 : 00:25:01

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.

tooba
Posting Yak Master

224 Posts

Posted - 2013-04-23 : 00:39:45

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 00:50:41

[code]-- 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[/code]

--
Chandu

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-23 : 08:26:34

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources