Max to find corresponding value

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 2008 Forums

Transact-SQL (2008)

Max to find corresponding value

Author

Topic

dutchgold647
Starting Member

13 Posts

Posted - 2014-11-18 : 03:06:16

hi there,

when i use the below query, i get a list of 6 accounts returned with exactly what i need.

how do i structure the query so it will give me the max lnid with the corresponding failscancels value?

when i run this query by itself, it returns:

LnID Person FailsCancels
119976 abcde-12345 4
122741 abcde-12345 2
128915 abcde-12345 0
131723 abcde-12345 1
135164 abcde-12345 0
139586 abcde-12345 0

i want it to just return:

LnID Person FailsCancels
139586 abcde-12345 0

i've tried wrapping it as a sub query and trying to select max(lnid) but can't get it to work.

the query i'm trying to use is:

SELECT LnID
,Person
,SUM(ISNULL(FailedPayments,0))+SUM(ISNULL(CancelledPayments,0)) AS FailsCancels

FROM Loan
LEFT JOIN (SELECT Trans_ID AS ID,COUNT(Trans_ID) AS Failed FROM Transaction WHERE TransStatus = 9 AND GROUP BY Trans_ID) AS Qry1 ON Qry1.ID = Loan. LnID
LEFT JOIN (SELECT Trans_ID AS ID,COUNT(Trans_ID) AS Cancelled FROM Transaction WHERE TransStatus = 8 AND GROUP BY Trans_ID) AS Qry2 ON Qry2.ID = Loan. LnID

WHERE Person = 'abcde-12345'

GROUP BY LnID,Person

many thanks for your help

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-11-18 : 06:37:10

Maybe:


SELECT TOP 1 -- etc
...
ORDER BY LnID DESC

DCTFUK
Starting Member

11 Posts

Posted - 2014-11-18 : 08:31:53

Select Max(LnID) ?

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-11-18 : 16:42:30

[code]
SELECT
l.LnID
, l.Person
, SUM(ISNULL(trans_qry.FailedPayments,0))+SUM(ISNULL(trans_qry.CancelledPayments,0)) AS FailsCancels
FROM (
SELECT Person, MAX(LnID) AS LnID
FROM Loan
WHERE
Person = 'abcde-12345'
GROUP BY Person
) AS l
LEFT JOIN (
SELECT
Trans_ID AS ID
, SUM(CASE WHEN TransStatus = 8 THEN 1 ELSE 0 END) AS Cancelled
, SUM(CASE WHEN TransStatus = 9 THEN 1 ELSE 0 END) AS Failed
FROM [Transaction]
WHERE TransStatus IN (8, 9)
GROUP BY Trans_ID
) AS trans_qry ON trans_qry.ID = l.LnID
ORDER BY LnID

[/code]

dutchgold647
Starting Member

13 Posts

Posted - 2014-11-18 : 20:38:56

hi ifor & dctfuk,

select top one won't work because when i have the query working, i will remove the 'where person ='

for some reason, when i use Max(LnID), it throws an error 'invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause'. When i then use an aggregate function such as max i get:

LnID Person FailsCancels
139586 abcde-12345 4

where as i want:

LnID Person FailsCancels
139586 abcde-12345 0

@ScottPletcher: when i try your way i get the following 'Operand type clash: uniqueidentifier is incompatible with int'. i imagine that's because the column person is a unique identifier.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources