Hi,

I am having trouble trying to find the max of 2 columns in one table. I've tried using a common table expression and a subquery, but can't seem to get the correct results. I want to get the max from refnum, then the max "number" associated with that max refnum along with the date and decision

Table

ID	Customer	Refnum	number	date 	decision
1651	1	1	1	4/17/2015	Approved
1652	1	1	2	5/1/2015	Declined
1653	1	2	1	6/10/2015	Approved
1654	2	1	1	6/15/2015	Tentative

Expected

Customer 1 had a max of refnum of 2 and 1st one on number

ID	Customer	Refnum	number	date 	decision
1653	1	2	1	6/10/2015	Approved
1654	2	1	1	6/15/2015	Tentative

I don't understand your description and result. For the customer 1 the max RefNum is 2 which is 'Declined'. Did you want to show that row? If not, how did you arrive to your result? First result after that max RefNum? 

Also, I just realized that in your picture the number is simply missing.

I think the values of Number column are missing.

Post the DDL as follows:

tableMyTable (id int,customer int,refnum int,number int,datedate,decision nvarchar(50))

Try the below?

create Table testTable (
ID int,	Customer int,	Refnum int,	number int,	sdate date, 	decision varchar(20))

Insert into testTable Values
(1651,1,1,1,'4/17/2015','Approved'),
(1652,1,1,2,'5/1/2015','Declined'),
(1653,1,2,1,'6/10/2015','Approved'),
(1654,2,1,1,'6/15/2015','Tentative')

;with cte as
(
Select *,row_number()Over(partition by Customer Order by sdate desc) Rn
From testtable
)
Select * From cte where rn = 1

Drop table testtable

This perhaps?  Also, keep in mind the creation of the table and sample data is something you should always do, it makes it easier for people to help you.

Select * INTO ##SAMPLE From (  Values 
		(1651,	1,	1,	1,	'4/17/2015',	'Approved')
		, (1652,	1,	1,	2,	'5/1/2015',		'Declined')
		, (1653,	1,	2,	1,	'6/10/2015',	'Approved')
		, (1654,	2,	1,	1,	'6/15/2015',	'Tentative')
		, (56461,	101,	1,	1,	'6/25/2015',	'Tentative')
		, (56462,	101,	2,	1,	'6/25/2015',	'Tentative')
		, (56463,	101,	3,	1,	'6/25/2015',	'Tentative')
		, (56463,	101,	3,	2,	'6/25/2015',	'Tentative')
	) as VT(ID, Customer, RefNum, Number, Date, Decision)

;With Pass1 as 
	(Select *, Max(RefNum) over(Partition by Customer) as MaxRefNo, max(Number) over(partition by Customer, RefNum) as MaxNumber
	from ##SAMPLE)
Select *
  from Pass1
  where RefNum = MaxRefNo and Number = MaxNumber

Results

ID	Customer	RefNum	Number	Date	Decision	MaxRefNo	MaxNumber
1653	1	2	1	6/10/2015	Approved	2	1
1654	2	1	1	6/15/2015	Tentative	1	1
56463	101	3	2	6/25/2015	Tentative	3	2

• Edited by johnqflorida 17 hours 29 minutes ago
• Proposed as answer by HarSan Online 14 hours 15 minutes ago

I apologize all!

I had been working all last night with no sleep and was a little delirious when I was asking this question. Here is more clarification on what i'm trying to do. 

The RefNum is a reference number and the Number column is tied to those RefNum when a decision is added. So we could have the first reference number 1 and then 3 decisions tied to this and reference 2 and 5 decisions to this. My struggle is I don't know how to accomplish pulling the latest decision. I understand how to pull the max of one field just not sure how to for 2 fields. 

Additional data

create Table testTable (
ID int,	Customer int,	Refnum int,	number int,	sdate date, 	decision varchar(20))

Insert into testTable Values
(1651,1,1,1,'4/17/2015','Approved'),
(1652,1,1,2,'5/1/2015','Declined'),
(1653,1,2,1,'6/10/2015','Approved'),
(1654,2,1,1,'6/15/2015','Tentative'),
(1655,3,1,1,'6/15/2015','Tentative'),
(1656,3,1,2,'6/15/2015','Rejected'),
(1657,3,2,1,'6/15/2015','Approved'),
(1658,3,2,2,'6/15/2015','Declined'),
(1659,3,2,3,'6/15/2015','Tentative'),
(1660,3,2,4,'6/15/2015','Approved')

Expected result

ID	Customer	Refnum	number	sdate	decision
1653	1	2	1	2015-06-10	Approved
1654	2	1	1	2015-06-15	Tentative
1660	3	2	4	2015-06-15	Approved

Customer 1, max refnum 2 and max number is 1

customer 2, max refnum 1 and max number is 1

customer 3, max refnum 2 and max number is 4

Hopefully this helps and Thank you all for replying and assistance!

In this case it's very simple using ROW_NUMBER() approach, e.g.

;with cte as (select *, row_number() over (partition by Customer, RefNum order by sDate DESC) as Rn from testTable)

select * from cte where Rn = 1 -- latest date for customer and RefNum

You may also want to read this very good blog with many solutions to that problem:

Including an Aggregated Columns Related Values

In this case it's very simple using ROW_NUMBER() approach, e.g.

;with cte as (select *, row_number() over (partition by Customer, RefNum order by sDate DESC) as Rn from testTable)

select * from cte where Rn = 1 -- latest date for customer and RefNum

You may also want to read this very good blog with many solutions to that problem:

Including an Aggregated Columns Related Values

Hi,

Try:

with CTE_RN as
(
    select
        *,
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY Refnum DESC, number DESC) as RN
    from testTable
)

select * from CTE_RN
where RN = 1

Hope this helps.

Like this?

DECLARE @table TABLE (ID INT, Customer INT, Refnum INT, number INT, date DATE, decision VARCHAR(20))
INSERT INTO @table (ID, Customer, Refnum, number, date, decision) VALUES
(1651, 1, 1, 1, '4/17/2015', 'Approved '),
(1652, 1, 1, 2, '5/1/2015',  'Declined '),
(1653, 1, 2, 1, '6/10/2015', 'Approved '),
(1654, 2, 1, 1, '6/15/2015', 'Tentative')

;WITH maxRef AS (
SELECT ID, MAX(refNum) AS mRefNum
  FROM @table
 GROUP BY ID),
 maxNumber AS (
SELECT ID, MAX(number) AS mNumber
  FROM @table
 GROUP BY ID)

SELECT *
  FROM @table t
    INNER JOIN maxRef r
	  ON t.ID = r.ID
	INNER JOIN maxNumber n
	  ON t.id = n.ID

In all your pictures the number is always missing so it's making harder to understand you.

@Naomi - it's an optical illusion because of column alignment.  The NUMBER data appears under the date.

@The OP - Take a look at my earlier post.  It's another path to the answer you're looking for, I believe it does what you described.

the ID's are only 4 digits, there's a hidden tab in there.

ID		Customer	Refnum	number	sdate		decision
1653		1		2		1		2015-06-10	Approved
1654		2		1		1		2015-06-15	Tentative
1660		3		2		4		2015-06-15	Approved

Well, with Patrik's explanation, are you simply looking for

;with cte as (select *, row_number() over (partition by Customer order by Refnum DESC, Number DESC) as Rn

from myData)

select * from cte where Rn = 1 -- max RefNum and max number under that max RefNum for a customer

Hi,

Try:

with CTE_RN as
(
    select
        *,
        ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY Refnum DESC, number DESC) as RN
    from testTable
)

select * from CTE_RN
where RN = 1

Hope this

In all your pictures the number is always missing so it's making harder to understand you.

@Naomi - it's an optical illusion because of column alignment.  The NUMBER data appears under the date.

@The OP - Take a look at my earlier post.  It's another path to the answer you're looking for, I believe it does what you described.

I apologize, I ran the output in SQL > Insert Code block > SQL > Pasted

It looked good in the preview. Is there a better way to do this?

Well, with Patrik's explanation, are you simply looking for

;with cte as (select *, row_number() over (partition by Customer order by Refnum DESC, Number DESC) as Rn

from myData)

select * from cte where Rn = 1 -- max RefNum and max number under that max RefNum for a cu

I am not sure. Probably manually insert data without code block?

If I would have seen the picture clearly from the start, I would have answered right away. But in any case, did you check the blog I pointed you to? It should have showed you many solutions of this very common problem.