• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Unique ID for max Amount in group

Hi Experts,

I have the following table:

ID || Amount || Group || Maturity Date
1    20            25           01/01/01
2    30            25           01/02/01
3    50            26           01/02/02
4    50            26           01/02/02
5    100          27           01/02/03
6    100          27           03/01/03

I need to get:
2    30            25           01/02/01
4    50            26           01/02/02
6    100          27           03/01/03

The rules how this subset is chosen are :
I need to return ID for Maximum amounts in each group.
If there are 2 or more records with same max amount and group - then pick with older Maturity Date.
If there are two or more records  with same max Amount, group AND Maturity Date - then pick max ID.


0
PTishyn_com
Asked:
PTishyn_com
1 Solution
 
Rey Obrero (Capricorn1)Commented:
try this query

SELECT Max(YourTable.ID) AS MaxOfID, Max(YourTable.Amount) AS MaxOfAmount, YourTable.Group, Max(YourTable.MaturityDate) AS MaxOfMaturityDate
FROM YourTable
GROUP BY YourTable.Group;
0
 
Rey Obrero (Capricorn1)Commented:
scrap that query...
0
 
djon2003Commented:
Here is something that should fits your needs.

This has been tested in SQL Server 2005 Express. Though, the SELECT query should be compatible.
DECLARE @MyTable as TABLE (id int, amount int, groupid int, maturity datetime)

INSERT INTO @MyTable
SELECT 1,    20,            25,           '01/01/01'
UNION ALL SELECT 2,    30,            25   ,        '01/02/01'
UNION ALL SELECT 3,    50,            26  ,         '01/02/02'
UNION ALL SELECT 4,    50,            26 ,          '01/02/02'
UNION ALL SELECT 5,    100,          27 ,          '01/02/03'
UNION ALL SELECT 6,    100 ,         27,           '03/01/03';


SELECT [@MyTable].*
FROM @MyTable, (SELECT groupid, max(amount) as maxAmount, max(maturity) as maxMaturity, max(id) as maxId
FROM @MyTable
GROUP BY groupid) as maxTable 
WHERE maxTable.groupid = [@MyTable].groupid AND maxTable.maxAmount = amount
AND maxMaturity = maturity AND maxId = id

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
djon2003Commented:
Ehm, finally this query won't always work.

If we inverse the Id 5 and 6, then the last row doesn't come out.

Removing "AND maxMaturity = maturity AND maxId = id" from the query makes it partially works.


I think this is a good start... probably adding SELECT COUNT for the other rules.
0
 
peter57rCommented:
Select   t1.* from table as t1 where
t1.ID in   (Select Top 1 [ID] from table as t2
where t2.[group] = t1.[group]
Order By t2.Amount Desc, t2.[Maturity Date], t2.[ID] Desc)
Order By T1.[group]
0
 
Rey Obrero (Capricorn1)Commented:
try this query
SELECT T.ID, T.Amount, T.Group, T.MaturityDate
FROM 
	((YourTable AS T
       INNER JOIN
	(SELECT Max(T1.Amount) AS MaxOfAmount, T1.Group
	FROM YourTable As T1
	GROUP BY T1.Group) As T2
	ON T.Amount=T2.MaxOfAmount)
       INNER JOIN
	(SELECT T3.Amount, T3.Group, Max(T3.MaturityDate) AS MaxOfMaturityDate
	FROM YourTable As T3
	GROUP BY T3.Amount, T3.Group) As T4
	ON T.MaturityDate=T4.MaxOfMaturityDate)
       INNER JOIN
	(SELECT Max(T5.ID) AS MaxOfID, T5.Amount, T5.Group, T5.MaturityDate
	FROM YourTable As T5
	GROUP BY T5.Amount, T5.Group, T5.MaturityDate) As T6
	ON T.ID=T6.MaxOfID

Open in new window

0
 
djon2003Commented:
Wow ! Really great job capricorn1. I though about that somewhat, but it was too late to continue investigating. Hoping my draft helped you a bit.
0
 
GRayLCommented:
Well done Rey.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now