The MAX row - 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 2005 Forums

Transact-SQL (2005)

The MAX row

Author

Topic

madscientist
Starting Member

30 Posts

Posted - 2008-09-11 : 20:49:57

Hello everyone,

I have a problem that is stumping me. I have a table called NAME with 4 fields. Looks like this:

[POLICY_NUMBER], [DATE], [TRANSACTION], [MAX]

BBT-WC-0010789-1, 1/28/2008 1:55:46 PM, 02, 1
CGT-WC-0010121-1, 5/31/2007 2:38:52 AM, 10, 2
AMS-WC-0010345-0, 6/10/2008 2:57:24 PM, 01, 1
AMS-WC-0010345-0, 6/10/2008 2:57:24 PM, 01, 2
.
.
.

I would like to separate out the identical rows (i.e. the bottom 2 in the above example based on the higher value in the [MAX] field. The results would look like:

BBT-WC-0010789-1, 1/28/2008 1:55:46 PM, 02, 1
CGT-WC-0010121-1, 5/31/2007 2:38:52 AM, 10, 2
AMS-WC-0010345-0, 6/10/2008 2:57:24 PM, 01, 2

Ideally deleting the other row from the table. Thank you very much for the help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-11 : 20:57:43

[code]
DELETE [D]
FROM
(
select [POLICY_NUMBER], [DATE], [TRANSACTION], [MAX],
[ROW_NO] = row_number() over (partition by [POLICY_NUMBER], [DATE], [TRANSACTION]
order by [MAX] desc)
from [NAME]
) as [D]
WHERE [D].[ROW_NO] <> 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-11 : 22:32:39

or
select [POLICY_NUMBER], [DATE], [TRANSACTION], [MAX],
[ROW_NO] = rank() over (partition by [POLICY_NUMBER], [DATE], [TRANSACTION]
order by [MAX] desc)
from [NAME]

hey

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-09-12 : 00:17:51

quote:
Originally posted by hey001us

or
select [POLICY_NUMBER], [DATE], [TRANSACTION], [MAX],
[ROW_NO] = rank() over (partition by [POLICY_NUMBER], [DATE], [TRANSACTION]
order by [MAX] desc)
from [NAME]

hey

In this method You would still need to filter out row_No > 1 Also although the rank() function works on this data, it's not really a ranking scenerio as much as it is a Row numbering, the Row_Number() is probably better fitting if you want to be as close to the BOL's examples/methods (Although I am not certain if there would be any differance in the actual execution)


Select 
[POLICY_NUMBER]
, [DATE]
, [TRANSACTION]
, [MAX]
from
(
select 
[POLICY_NUMBER]
, [DATE]
, [TRANSACTION]
, [MAX],
Row_Number() over (partition by [POLICY_NUMBER], [DATE], [TRANSACTION] order by [MAX] desc) as Row_No
from [name]
) a
where a.Row_No = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources