September 7, 2019 at 12:53 am
Hi,
I have the following table:
CREATE TABLE [dbo].[test](
[ID] [varchar](10) NOT NULL,
[RN] [decimal](10, 0) NULL,
[PERSON_ID] [varchar](10) NULL,
[WIDGET_ID] [varchar](10) NULL,
[AMOUNT] [decimal](14, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST', CAST(1 AS Decimal(10, 0)), N'1000000', N'5353288', NULL)
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST', CAST(2 AS Decimal(10, 0)), N'1234567', N'5325323', CAST(50.00 AS Decimal(14, 2)))
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST2', CAST(1 AS Decimal(10, 0)), N'8888888', N'0498209', CAST(10.00 AS Decimal(14, 2)))
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST2', CAST(2 AS Decimal(10, 0)), N'9999999', N'3523580', CAST(10.00 AS Decimal(14, 2)))
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST2', CAST(3 AS Decimal(10, 0)), N'0000000', NULL, NULL)
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST3', CAST(1 AS Decimal(10, 0)), N'4444444', N'4983274', NULL)
GO
INSERT [dbo].[test] ([ID], [RN], [PERSON_ID], [WIDGET_ID], [AMOUNT]) VALUES (N'TEST3', CAST(2 AS Decimal(10, 0)), N'0200202', N'4239789', NULL)
GO
SELECT *
FROM test
ORDER BY ID, RN
I am trying to find the first record per ID that has a non-null AMOUNT, and display the corresponding PERSON_ID for each WIDGET_ID. If there isn't a non-null amount for an ID, then display the corresponding PERSON_ID where RN = 1 for each record of that WIDGET_ID.
Using the same example, the result set would look like:
WIDGET_ID, PERSON_ID
5353288, 1234567
5325323, 1234567
0498209, 8888888
3523580, 8888888
4983274, 4444444
4239789, 4444444
I have a convoluted way I can do this but I know there's an easier way. It's Friday, and I just can't seem to think of an efficient way without querying the table more than once. Can anyone help?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 7, 2019 at 5:59 am
If you want it in one single query, something like this..
Works for your sample cases, test for few more
select
fn.widget_id,
max(final.person_id) as person_id
from
#test fn
left join
(
select main.*
from
(
select min(t.rn) as rn,t.id as id,t.amount as amount
from
(
select id, max(isnull(amount,0)) as amnt
from #test
group by id
)a
join #test t on t.id = a.id and isnull(t.amount,0) = a.amnt
group by t.id,t.amount
)b
join #test main on main.id = b.id and isnull(main.amount,0) = isnull(b.amount,0)
and main.rn = b.rn
)final
on final.id = fn.id
where fn.widget_id is not null
group by fn.widget_id
September 7, 2019 at 1:52 pm
Thank you so much for your response! Your query gave me some good ideas. I'll need to test it a bit more, but I think this will do it!
SELECT WIDGET_ID, FIRST_VALUE(PERSON_ID) OVER(PARTITION BY ID ORDER BY CASE WHEN AMOUNT IS NULL THEN 1 ELSE 0 END, RN) AS PERSON_ID
FROM test
WHERE WIDGET_ID IS NOT NULL
ORDER BY ID, RN
Again, thank you.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
September 13, 2019 at 1:36 pm
a slightly easier way could be using row_number over order by
for example, to get the first item in sysobjects by type (first table, first proc etc)
select x.* from (
select name, row_number() over (partition by xtype order by name) AS rnk FROM sysobjects
) AS x WHERE x.rnk=1
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply