April 21, 2017 at 8:06 pm
Hi Guys,
I am new here, Below is sample code. As an example from below, I want one unique records (id,client_id) where amount should be higher.
cREATE TABLE ##TEMP(
ID INT,
Client_ID int
,amount decimal(10,2)
)
insert into ##TEMP
select 1,234,0.00
union
select 1,856,100.00
union
select 1,924,400.00
union
select 2,234,0.00
union
select 2,856,0.00
union
select 2,924,0.00
select * from ##TEMP
Here is final result I am looking
ID,Client_ID,Amount
1,924,400.00
2,234,0.00
I want unique Row by ID, With the highest Rate, If Rates are same or 0.00 pick the lowest client_id
Thank You.
April 21, 2017 at 9:44 pm
SELECT Client_ID
, MAX([Amount]) AS MaxAmount
FROM ##TEMP
WHERE Amount>0
GROUP BY Client_ID;
April 21, 2017 at 9:59 pm
Hi Pietinden,
Thank You for your reply. However, I want the ID to from the result set, Once I get the ID. The step I am linking other table using that ID.IF I use ID in select and group by result change.
Here is the result I am getting
ID Client_ID MaxAmount
1 856 100.00
1 924 400.00
Here is final result I am looking
ID,Client_ID,Amount
1,924,400.00
2,234,0.00
SELECT ID,Client_ID
, MAX([Amount]) AS MaxAmount
FROM ##TEMP
WHERE Amount>0
GROUP BY Client_ID,ID;
April 21, 2017 at 10:57 pm
Then explain in plain English how that's supposed to happen. Once I know that, I can probably code it.
April 22, 2017 at 1:12 am
I apology if my question or comments are not clear. Let me try one more time. I want Unique ID and Client ID. To Pick the right Client ID the logic would be to Pick the highest rate. If the Amount is 0.00 then pick the lowest client id.
My source sample data
ID Client_ID MaxAmount
1,234,0.00
1,856,100.00
1,924,400.00
2,234,0.00
2,856,0.00
2,924,0.00
What I want from ID = 1
ID Client_ID MaxAmount
1,924,400.00
What I want from ID = 2
ID Client_ID MaxAmount
2,234,0.00
Please feel free to let me know if question still not clear yet.
BTW thank you for your help on this!
April 22, 2017 at 10:32 am
Consumable test data:
CREATE TABLE #t
(
ID int NOT NULL
,Client_ID int NOT NULL
,MaxAmount money NOT NULL
);
INSERT INTO #t
VALUES (1, 234, 0)
,(1, 856, 100)
,(1, 924, 400)
,(2, 234, 0)
,(2, 856, 0)
,(2, 924, 0);
The following approach works:
WITH OrderedData
AS
(
SELECT ID, Client_ID, MaxAmount
,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY MaxAmount DESC, Client_ID) AS rn
FROM #t
)
SELECT ID, Client_ID, MaxAmount
FROM OrderedData
WHERE rn = 1;
April 22, 2017 at 10:53 am
Hi Ken,
Thank You. That query works perfectly in my above example. I just Pull LIVE data below.
I tried to use above query on below LIVE data and it is not behaving as suppose to be. Can you please take a look?
Thank You.
CREATE TABLE [dbo].[#t](
[id] [int] NULL,
[client_id] [int] NULL,
[amount] [decimal](10, 2) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966781, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966781, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966781, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966781, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966781, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966782, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966782, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966782, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966782, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966782, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966783, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966783, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966783, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966783, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966783, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966784, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966784, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966784, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966784, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966784, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966785, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966785, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966785, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966785, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966785, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966786, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966786, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966786, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966786, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966786, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966787, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966787, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966787, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966787, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966787, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966788, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966788, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966788, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966788, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966788, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966789, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966789, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966789, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966789, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966789, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966790, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966790, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966790, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966790, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966790, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56132489, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56132489, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56132489, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56132489, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56132489, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56132490, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56132490, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56132490, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56132490, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56132490, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150224, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150224, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150224, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150224, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150224, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150225, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150225, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150225, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150225, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150225, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150226, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150226, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150226, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150226, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150226, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150227, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150227, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150227, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150227, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150227, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150228, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150228, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150228, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150228, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150228, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150229, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150229, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150229, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150229, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150229, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150230, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150230, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150230, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150230, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150230, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150231, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150231, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150231, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150231, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150231, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150232, CAST(0.00 AS Decimal(10, 2)))
GO
print 'Processed 100 total records'
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150232, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150232, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150232, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150232, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150233, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150233, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150233, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150233, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150233, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150234, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150234, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150234, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150234, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150234, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150235, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150235, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150235, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150235, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150235, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 56150236, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 56150236, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 56150236, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 56150236, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 56150236, CAST(0.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966771, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966771, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966771, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966771, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966771, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966772, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966772, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966772, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966772, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966772, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966773, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966773, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966773, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966773, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966773, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966774, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966774, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966774, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966774, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966774, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966775, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966775, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966775, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966775, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966775, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966776, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966776, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966776, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966776, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966776, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966777, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966777, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966777, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966777, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966777, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966778, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966778, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966778, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966778, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966778, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966779, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966779, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966779, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966779, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966779, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093827, 55966780, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093831, 55966780, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093846, 55966780, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093840, 55966780, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093835, 55966780, CAST(100.00 AS Decimal(10, 2)))
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093851, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093828, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093829, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093830, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093832, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093845, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093847, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093848, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093849, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093850, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093839, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093841, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093842, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093843, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093844, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093833, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093834, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093836, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093837, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093838, NULL, NULL)
INSERT [dbo].[#t] ([id], [client_id], [amount]) VALUES (2093852, NULL, NULL)
April 22, 2017 at 2:40 pm
I am sorry. It was TYPO. I will fix the comment. It should be "It is not behaving as suppose to be"
Sorry about that!
April 22, 2017 at 2:46 pm
mikesql710 - Saturday, April 22, 2017 2:40 PMI am sorry. It was TYPO. I will fix the comment. It should be "It is not behaving as suppose to be"
Sorry about that!
Yes, but as I asked, why is it not? What results are wrong, and why are they wrong?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 22, 2017 at 3:06 pm
Here what the end result should look like from above sample data. 26 Unique IDs. Here is the logic I need to pick to right info
It could possible one same id has a bunch of client_ids attached to pick the right client id the logic would be Pick the highest amount if an amount is 0.00 then pick the lowest client_id. If you ask me why is behaving as suppose to be. I have no idea.
Thanks for helping me out. Please let me know if my question is not still clear yet.
id client_id amount
2093852 NULL NULL
2093851 NULL NULL
2093837 55966771 100.00
2093827 55966772 100.00
2093834 56150226 NULL
2093841 55966774 100.00
2093847 55966775 100.00
2093833 55966776 100.00
2093838 55966777 100.00
2093828 55966778 100.00
2093842 55966779 100.00
2093848 55966780 100.00
2093839 55966781 0.00
2093843 55966782 0.00
2093845 55966783 0.00
2093829 55966784 0.00
2093849 55966785 0.00
2093831 55966786 0.00
2093835 55966787 0.00
2093844 55966788 0.00
2093850 55966789 0.00
2093846 55966790 0.00
2093840 56132489 0.00
2093830 56132490 0.00
2093832 56150224 0.00
2093836 56150225 0.00
April 22, 2017 at 3:20 pm
Plucking a random example from your above post how do you expect the get the row:id client_id amount
------- --------- ------
2093838 55966777 100.00
Looking at your sample INSERT statement, the only time that id 2093838 is inserted is with Client_id NULL and amount NULL. How are you getting client_id 55966777 from id 2093838 from the above data?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 22, 2017 at 3:37 pm
Thomas,
That was a final layout I just created to show you guys but looks like I screwed up a little bit. But the end result should be 26 Unique IDs and 24 Unique client ids and 2 Null client ids.
April 22, 2017 at 3:59 pm
I think you need to explain your logic further then. I really don't understand how you're linking your id's and client_ids in your expected out. Although I gave the example of 2093838, the following ID's only have NULLs for client_id and amount inserted; how do you get a client_id and amount value for these values when they have none in hour data:
2093851
2093828
2093829
2093830
2093832
2093845
2093847
2093848
2093849
2093850
2093839
2093841
2093842
2093843
2093844
2093833
2093834
2093836
2093837
2093838
2093852
The code that Ken gave you works as you requested for your original data and request, and does what is expected with your new data. It seems like your new data is either incomplete/incorrect, or your request is different to what you originally asked.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 22, 2017 at 11:20 pm
Is it that you are trying to join this result set with another table?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply