selecting max of 2 records per account
-
Hi,
I'm trying to select the max of two records per account, I tried the following but can't get it to work and I'm not experienced enough to do this. Thanks in advance.
select acct_number , idx, stage
from dbo.u_external_requests
where stage in (5,6,7)
group by acct_number, idx, stage
having count(idx)< 3 order by accnt_number
acct_number idx stage
-------------- ----------- -----------
1000000101 1 5
1000000179 1 7
1000000179 2 1
1000000179 3 5
1000000192 1 7
1000000192 4 8
1000000192 5 1
1000000192 6 8
1000000205 1 8
1000000205 5 7
1000000689 1 8
1000000689 3 7
1000000689 4 8
I was not too clear as to what I needed, supposed I have a table like the one above, this is what I would like to extract: see record set below
a) if an account has only one record and stage is in (5,6,7) the display it. i.e. record 1000000101.
b) if an account has more than one record only display the max of records per account where stage is in (5,6,7) i.e. record 1000000179
acct_number idx stage
-------------- ----------- -----------
1000000101 1 5
1000000179 1 7
1000000179 3 5
1000000192 1 7
1000000205 5 7
1000000689 3 7
-
without knowing what the rest of the structure that the query reference is like, or what kind of data it holds, its a hard ask.
I can only assume that lnkey is the primary key field of the table, if so then the below may work.
select acct_number , idx, stage
from dbo.u_external_requests
where category in (5,6,7)
and lnkey in (select top 2 limiter.lnkey
from dbo.u_external_requests limiter
where limiter.acct_number = dbo.u_external_requests.acct_number)
-
I think that will only match the TOP 2 lnkey's and hence only return those values. I was looking at this last night and having a great deal of difficulty. By GROUPing that string, you are in essence getting a COUNT of one for each record; hence COUNT() < 3 will always return all values. You will note with the SELECT I wrote before that only those records which have less than three acct_number entries match the criterion - not what you want, but it does show you what is happening with the GROUPing.
I believe this will require a loop through the table and I am looking at that. Hopefully, someone smarter than I will figure out a one pass through select....
I have omitted category and lnkey from my testing as I do not see them in the output and I am guessing the code below will work even when these are included.
CREATE TABLE #u_external_requests( acct_number int,
idx int,
stage int)
INSERT INTO #u_external_requests VALUES( 1000000179, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000179, 2, 1)
INSERT INTO #u_external_requests VALUES( 1000000192, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000192, 4, 8)
INSERT INTO #u_external_requests VALUES( 1000000192, 5, 1)
INSERT INTO #u_external_requests VALUES( 1000000192, 6, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 5, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000689, 3, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 4, 8)
SELECT acct_number, idx, stage
FROM #u_external_requests
WHERE acct_number IN( SELECT acct_number
FROM #u_external_requests
GROUP BY acct_number
HAVING COUNT( acct_number) < 3)
ORDER BY acct_number
DROP TABLE #u_external_requests
I wasn't born stupid - I had to study.
-
Alright. I may be going around the barn to get to the horse, (or however the saying goes), but this seems to work. Hopefully, someone will figure out a more elegant and streamline method.
CREATE TABLE #u_external_requests( acct_number int,
idx int,
stage int)
INSERT INTO #u_external_requests VALUES( 1000000179, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000179, 2, 1)
INSERT INTO #u_external_requests VALUES( 1000000192, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000192, 4, 8)
INSERT INTO #u_external_requests VALUES( 1000000192, 5, 1)
INSERT INTO #u_external_requests VALUES( 1000000192, 6, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 5, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000689, 3, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 4, 8)
INSERT INTO #u_external_requests VALUES( 1000000705, 1, 7) -- Test values for only one entry....
CREATE TABLE #OutPut( acct_number int,
idx int,
stage int)
DECLARE @CurrentAcctNum int,
@MaxAcctNum int
SELECT @CurrentAcctNum = (SELECT MIN( acct_number) FROM #u_external_requests)
SELECT @MaxAcctNum = (SELECT MAX( acct_number) FROM #u_external_requests)
WHILE @CurrentAcctNum <= @MaxAcctNum
BEGIN
INSERT INTO #OutPut
SELECT TOP 2 acct_number, idx, stage
FROM #u_external_requests
WHERE acct_number = @CurrentAcctNum
SELECT @CurrentAcctNum = (SELECT MIN( acct_number)
FROM #u_external_requests
WHERE acct_number > @CurrentAcctNum)
END
SELECT acct_number, idx, stage
FROM #OutPut
DROP TABLE #OutPut
DROP TABLE #u_external_requests
I wasn't born stupid - I had to study.
-
Basically the same as Nick M's suggestion. But with ORDER BY's.
set nocount on
use northwind
select
t1.CustomerID
, t1.OrderDate
from
orders t1
where
t1.OrderDate in
(
select top 2 --with ties
t2.OrderDate
from
orders t2
where
t2.CustomerID = t1.CustomerID
order by
t2.OrderDate desc
)
order by
t1.CustomerID
, t1.OrderDate desc
set nocount off
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I see how the JOIN works with your code, but the original data had no date to use. If idx or stage had business rules similar to date, (i.e., like the top two stages) that could be used. But I did not see any indication of that in the original post - hence my sledge hammer approach.
Hopefully Nick, (without the M) will elucidate upon the data and give us some direction on using one of the fields. I was hoping to see something along the lines of you and Nick M as I know my approach is too heavy handed....
I wasn't born stupid - I had to study.
-
thank you for your time, I have edited initial request cause I was not too clear as to what I need.
-
So the top 2 isn't the issue? The stage of the record is the defining field? In other words, if an account is in stage 5, 6, or 7, you want all three records displayed?
I wasn't born stupid - I had to study.
-
No, I just want 2 records out of the three.
-
You should be able to try my approach with the addition of the following:
INSERT INTO #OutPut
SELECT TOP 2 acct_number, idx, stage
FROM #u_external_requests
WHERE acct_number = @CurrentAcctNum
AND stage IN( 5, 6, 7)
I have not looked into Frank or Nick M's approach with that constraint...yet.
I wasn't born stupid - I had to study.
-
Using Frank and Nick M's approach, I got this.... [ Thanks guys!
]I changed some of the data to verify it will only retrieve the top 2 if all three exist.
CREATE TABLE #u_external_requests( acct_number int,
idx int,
stage int)
INSERT INTO #u_external_requests VALUES( 1000000179, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000179, 2, 6)
INSERT INTO #u_external_requests VALUES( 1000000192, 1, 7)
INSERT INTO #u_external_requests VALUES( 1000000192, 4, 6)
INSERT INTO #u_external_requests VALUES( 1000000192, 5, 5)
INSERT INTO #u_external_requests VALUES( 1000000192, 6, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000205, 5, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 1, 8)
INSERT INTO #u_external_requests VALUES( 1000000689, 3, 7)
INSERT INTO #u_external_requests VALUES( 1000000689, 4, 8)
INSERT INTO #u_external_requests VALUES( 1000000705, 1, 7) -- Test values for only one entry....
SELECT uer.acct_number, uer.idx, uer.stage
FROM #u_external_requests uer
WHERE uer.stage IN( SELECT TOP 2 u.stage
FROM #u_external_requests u
WHERE uer.acct_number = u.acct_number
AND u.stage IN( 5, 6, 7)
ORDER BY u.stage DESC)
DROP TABLE #u_external_requests
I wasn't born stupid - I had to study.
-
this should do the trick. modify at will to include other data...
select a.*
from dbo.u_external_requests a
where a.stage in (
select top 2 stage from dbo.u_external_requests b
where b.acct_number = a.acct_number
and b.stage in (5,6,7)
)
oh, actually just noticed this is same as ~prior~ post. well, there you go.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply