Home > Enterprise >  SQL - select only max values for each timestamp
SQL - select only max values for each timestamp

Time:10-27

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
  • Related