I have to write a query that selects multiple values from a table and compares two of them. The larger one should be used.
example:
| TagName | Value |
|---|---|
| A1 | 20 |
| A2 | 30 |
| A3-1 | 15 |
| A3-2 | 20 |
I want A3-2 the result table as:
| TagName | Value |
|---|---|
| A1 | 20 |
| A2 | 30 |
| A3 | 20 |
How would I do that? Unfortunately, I have to do this comparison in SQL
Thank you :)
CodePudding user response：
You can combine MAX with LEFT function :
select distinct LEFT(TagName, 2) as IDs, max(valuee) over (partition by (LEFT(TagName, 2)))
from tablee
order by IDs ASC
HERE is fiddle but for posgresql, but it should work for you too, if not, at least this logic can be used
CodePudding user response：
If you have provided an example that corresponds to how the data looks in real life, you could try something like:
select substring(TagName, 1, 2) as TagName, max(Value) as Value from table group by TagName
This way you create a substring that will include all of the values from similar records, compare them & display the maximum one.