SQL SELECT for max and min values with virtual columns

Hi, i need help to create a query to:

I have this table

name	year	number	value
NAME1	2021	001406	189
NAME2	2022	001406	150
NAME3	2022	001406	800
NAME2	2020	001592	60
NAME4	2021	001592	89,25
NAME1	2022	001592	250
NAME2	2022	001592	50

I need that when a number appears more than one time in the same year, it shows the highest value in a column called MAXVALUE and a “virtual” column with the min value of the repeated rows, called MINVALUE. For example, in the table, VALUE 001592 appears three times; 2 for year 2022 and 1 for year 2020. The result must seen as:

year	number	maxvalue	minvalue
2022	001592	250	                 50
2021	001592	89,25	          0

Thanks a lot,

Hi,

First of all Thanks.

This works ok, but i need when only there’s 1 row, minvalue must be 0. If there are two rows with the same number in same year, max value must be the highest and minvalue the minimum… Butg when there’s only 1 row and by this only are one value, max value must be this unic value and in minvalue must appear as 0

Thanks again,

Try this and let me know how it goes :slight_smile:

SELECT year, number, MAX(value) AS maxvalue, 
CASE 
  WHEN COUNT(*) > 1 THEN MIN(value)
  ELSE 0
END AS minvalue
FROM table_name
GROUP BY year, number
HAVING COUNT(*) > 0;

here’s what i changed:

  • Use a CASE statement to set minvalue to 0 if there’s only one row for a group.
  • Keep the MAX() function to get the maximum value for each group.
  • Use the GROUP BY clause to group the data by year and number.
  • Use the HAVING clause to exclude groups with a count of 0.

It works like a charm. The only thing that when i try to join it with other tables, is giving me wrong results. I am going to see if I can put the structure of the tables that I use and thus be able to show what I am looking for. Many many thanks,

Hi,

Just works ok. There was an error in my conditions in the join.

Hi @yamadapanda , the error was mine. Many many thanks!!!

Can you help me with this other topic please?