Long term observer, first time poster.
I have a table that is:
| UTC | Allowed | Blocked |
|---|---|---|
| 1666852500 | 100 | 52 |
| 1666853100 | 45 | 11 |
| 1666853100 | 67 | 15 |
The UTC column has multiple duplicates, due to my sampling. I would like to remove duplicates of 'utc' and return the max value of Allowed and, or Blocked...for example
| UTC | Allowed | Blocked |
|---|---|---|
| 1666852500 | 100 | 52 |
| 1666853100 | 67 | 15 |
I'm using SQL, but for the life of me I can't process it in my head. Can't even think of the terminology to google, hence why here. Hopefully someone can advise.
SQL or python would be useful.
Thank you in advance.
Have attempted filtering in python with a bunch of for loops....Didn't workout... Have tried Nest SQL select queries. I believe it's something simple, but it eludes my tiny brain
CodePudding user response：
according to @Sergey it works well
select x.a,[b]=max(x.b),[c]=max(x.c) from (values('1666852500',100,52),('1666853100',45,11),('1666853100',67,15)) as x(a,b,c) group by x.a
Output is | a | b | c | |-|-|-| |1666852500|100|52| |1666853100|67|15
CodePudding user response：
SELECT UTC, Blocked, MAX(Allowed) FROM YourTable GROUP BY UTC