Prev: INSERT/SELECT - transaction?
Next: Fill leading zeros
From: pvong on 26 Apr 2010 17:11
Newbie trying to learn in SQL 2005

Table looks like this:

ObjectiveID ClientID Objective
100 1 Growth
101 2 Growth
102 1 50/50
103 2 Balance
104 1 Income
105 2 Fixed Income

All I want to do is run a query where every ClientID is shown only once with
the MAX ObjectiveID and it's corresponding Objective. The result should
only show me

104 1 Income
105 2 Fixed Income

I tried a Group By for ClientID and Objective with a Max on Objective ID,
but that still gives me every item. What am I doing wrong?
Thanks in advance.

Phil


From: dm on 26 Apr 2010 17:49
One way:

/*
create table #temp (ObjectiveID int,ClientID int, Objective
varchar(20))
insert into #temp(ObjectiveID,ClientID,Objective)
values(100,1,'Growth'),
(101,2,'Growth'),
(102,1,'50/50'),
(103,2,'Balance'),
(104,1,'Income'),
(105,2,'Fixed Income')
--*/

;WITH T1(ObjectiveID,ClientID) AS
(
SELECT MAX(ObjectiveID) AS ObjectiveID,ClientID from #temp group by
ClientID
)
SELECT t2.ObjectiveID,t2.ClientID,t2.Objective
FROM #temp t2
JOIN T1 t1 ON T1.ObjectiveID = t2.ObjectiveID and T1.ClientID =
t2.ClientID
ORDER BY t2.ObjectiveID,t2.ClientID;


OR can use


SELECT t1.ObjectiveID,t1.ClientID,t1.Objective
FROM #temp t1
JOIN (select MAX(ObjectiveID) AS ObjectiveID,ClientID
from #temp t2 group by ClientID)t2 ON t1.ObjectiveID =
t2.ObjectiveID and t1.ClientID = t2.ClientID
ORDER BY t1.ObjectiveID,t1.ClientID

From: Q on 26 Apr 2010 18:05
Here is one way:

DECLARE @ClientObjective TABLE (ObjectiveID int, ClientID int, Objective
varchar(50))

INSERT INTO @ClientObjective VALUES (100, 1, 'Growth')
INSERT INTO @ClientObjective VALUES (101, 2, 'Growth')
INSERT INTO @ClientObjective VALUES (102, 1, '50/50')
INSERT INTO @ClientObjective VALUES (103, 2, 'Balance')
INSERT INTO @ClientObjective VALUES (104, 1, 'Income')
INSERT INTO @ClientObjective VALUES (105, 2, 'Fixed Income')

SELECT o.ObjectiveID, o.ClientID, o.Objective
FROM @ClientObjective o
INNER JOIN
(
SELECT ClientID, MAX(ObjectiveID) AS ObjectiveID
FROM @ClientObjective
GROUP BY ClientID
) m ON m.ClientID = o.ClientID AND m.ObjectiveID = o.ObjectiveID
ORDER BY o.ObjectiveID


"pvong" wrote:

> Newbie trying to learn in SQL 2005
>
> Table looks like this:
>
> ObjectiveID ClientID Objective
> 100 1 Growth
> 101 2 Growth
> 102 1 50/50
> 103 2 Balance
> 104 1 Income
> 105 2 Fixed Income
>
> All I want to do is run a query where every ClientID is shown only once with
> the MAX ObjectiveID and it's corresponding Objective. The result should
> only show me
>
> 104 1 Income
> 105 2 Fixed Income
>
> I tried a Group By for ClientID and Objective with a Max on Objective ID,
> but that still gives me every item. What am I doing wrong?
> Thanks in advance.
>
> Phil
>
>
> .
>
From: Plamen Ratchev on 26 Apr 2010 18:20
On SQL Server 2005 you can use ROW_NUMBER:

SELECT ObjectiveID, ClientID, Objective
FROM (
SELECT ObjectiveID, ClientID, Objective,
ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY ObjectiveID DESC) AS rk
FROM MyTable) AS T
WHERE rk = 1;

--
Plamen Ratchev
http://www.SQLStudio.com
From: pvong on 27 Apr 2010 10:25
Thank you. This really helped me.


"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:qpqdnYPzYswvjEvWnZ2dnUVZ_qednZ2d(a)speakeasy.net...
> On SQL Server 2005 you can use ROW_NUMBER:
>
> SELECT ObjectiveID, ClientID, Objective
> FROM (
> SELECT ObjectiveID, ClientID, Objective,
> ROW_NUMBER() OVER(PARTITION BY ClientID ORDER BY ObjectiveID DESC)
> AS rk
> FROM MyTable) AS T
> WHERE rk = 1;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: INSERT/SELECT - transaction?
Next: Fill leading zeros