Advanced SQL Queries You May Know
Udgivet 2017-09-08 - Skrevet af
Advanced SQL Queries You May Know but Have to Look Up
The idea for this article came to me a day at work where I was looking up the same solution to a problem I had just had earlier that week. So I thought why not start collecting these queries that you occasionally use, but are still hard enough to require a look up.
I'll keep it updated with queries as I come across relevant ones.
Grouping by ranges of values
Let's say you have a table (Team) with 3 values, name, birth year, and salary. From this table you want to see how much people make based upon their age in ranges of 10. We start by creating a Common Table Expression where we generate the ranges as shown here:with Series as ( select generate_series(1950, 2010, 10) as r_from), Range as ( select r_from, (r_from + 9) as r_to from series)This generates 10 year ranges from 1950 to 2009, e.g. 1950-1959, 1960-1969 and so on.
What we want to do from here is associate each range with a number of team members and their average salaries as shown below.
select r_from, r_to, (select count(*) from Team where birth_year between r_from and r_to) as team_members, round( (select avg(salary) from Team where birth_year between r_from and r_to), 2) as salary_avg, (select min(salary) from Team where birth_year between r_from and r_to) as salary_min, (select max(salary) from Team where birth_year between r_from and r_to) as salary_maxfrom RangeThe above generates a table with the following columns, where I have included an example result for a range.
| r_from | r_to | team_members | salary_avg | salary_min | salary_max |
| 1980 | 1989 | 4 | $91,250 | $80,000 | $110,000 |
Retrieving the latest entry
This is for example retrieving the latest purchase a customer has made.The query finds the latest dates (max) for each customer (group by) and then joins it 1-1 with the table itself.
select t.username, t.date, t.valuefrom MyTable t inner join (select username, max(date) as MaxDate from MyTable group by username) tm on t.username = tm.username and t.date = tm.MaxDate
Do you think I missed a good query? Feel free to contact me.