Randy Banks Randy Banks - 3 months ago 11
SQL Question

Update the top n rows of a table in Postgres

I have the following table:

year | value | tercile
------+--------+---------
1980 | 96.4 |
1981 | 727 |
1982 | 804.7 |
1983 | 979 |
1984 | 995 |
1985 | 984.4 |
1986 | 994.6 |
1987 | 98.39 |
1988 | 97 |
1989 | 87.87 |
1990 | 4054.3 |
1991 | 82.89 |
1992 | 94 |
1993 | 97.61 |
1994 | 99.68 |
1995 | 99.9 |
1996 | 9.42 |
1997 | 75.66 |
1998 | 2.16 |
1999 | 696 |
2000 | 99.38 |
2001 | 9.81 |
2002 | 99.27 |
2003 | 2.75 |
2004 | 12.51 |
2005 | 9.29 |
2006 | 994.71 |
2007 | 97.06 |
2008 | 64.44 |
2009 | 95 |
2010 | 40.88 |
2011 | 9.46 |
2012 | 742.86 |
2013 | 9.5 |
2014 | 736.38 |
2015 | 93.13 |


I need to populate the 'tercile' column with values 1-3 according to the values in the 'value' column. 3 is assigned to the records with the 12 highest values, 2 is assigned to the records with the 12 middle values, and 1 is assigned to the records with the lowest 12 values.

I can retrieve these records with the following queries:

select * from trace_terciles order by value desc limit 12
select * from trace_terciles order by value desc limit 12 offset 12
select * from trace_terciles order by value desc limit 12 offset 24


But I cannot figure out how to update the results of these
SELECT
statements. How can I do this? By using a subquery?

Answer

How about

update trace_terciles set tercile=2 where year in (select year from trace_terciles order by value desc limit 12 offset 12)

and similarly for the other 2 cases.