![]() ![]() ( 10, 80700, 'Steve Ballmer', 'M', 'Technology', 'United States', 'Hunts Point', 'Microsoft', 'Technology', 'United States', 'Los Angeles Clippers', 'Owner', 1956 ) ( 9, 83400, 'Mukesh Ambani', 'M', 'Diversified', 'India', 'Mumbai', 'Diversified', 'Diversified', 'India', 'Reliance Industries', 'Founder and Chairman', 1957 ) , ( 8, 93000, 'Carlos Slim Helu & family', 'M', 'Telecom', 'Mexico', 'Mexico City', 'Telecom', 'Telecom', 'Mexico', 'América Móvil', 'Honorary Chairman', 1940 ) , ( 7, 94500, 'Michael Bloomberg', 'M', 'Media & Entertainment', 'United States', 'New York', 'Bloomberg LP', 'Media & Entertainment', 'United States', 'Bloomberg', 'CEO', 1942 ) , ( 6, 104000, 'Bill Gates', 'M', 'Technology', 'United States', 'Medina', 'Microsoft', 'Technology', 'United States', 'Bill & Melinda Gates Foundation', 'Cochair', 1955 ) , ( 5, 106000, 'Warren Buffett', 'M', 'Finance & Investments', 'United States', 'Omaha', 'Berkshire Hathaway', 'Finance & Investments', 'United States', 'Berkshire Hathaway Inc. ( 4, 107000, 'Larry Ellison', 'M', 'Technology', 'United States', 'Lanai', 'Oracle', 'Technology', 'United States', 'Oracle', 'CTO and Founder', 1944 ) , ( 3, 114000, 'Jeff Bezos', 'M', 'Technology', 'United States', 'Medina', 'Amazon', 'Technology', 'United States', 'Amazon', 'Chairman and Founder', 1964 ) , ( 1, 211000, 'Bernard Arnault & family', 'M', 'Fashion & Retail', 'France', 'Paris', 'LVMH', 'Fashion & Retail', 'France', 'LVMH Moët Hennessy Louis Vuitton', 'Chairman and CEO', 1949 ) , Source, industries, citizenship_country, organization, title, birth_year Rank, worth, name, gender, category, country, city , The data of the 10 (or 100) billionaires can be inserted with the following insert statement: INSERT INTO "billionaire" ( The goal of this tutorial is to understand the Postgres string_agg function, not something else. I have deliberately kept the structure very simple and have not added any index or unique indexes to the table as it will have only 100 rows. I also took only the top 100 billionaires from that list to create a Postgres table as follows: CREATE TABLE "billionaire" ( I downloaded the CSV and removed most of the fields not relevant to this example. Thankfully, Kaggle has a billionaires dataset of 2023 that lists 2540 billionaires in the world with many fields. Yes, you heard it right, the top 100 billionaires of the world. Now that we have a clear understanding of what string_agg is and how it compares to group_concat, let's move on to some practical examples using real data of billionaires of the world. Here the query will get the distinct test_score highest first grouped by student and separated by a space character. However, there are some syntax and implementation differences between the two.īelow is an example of the usage of group_concat taken directly form the MySQL docs: SELECT student_name , They both allow you to aggregate and concatenate values from multiple rows into a single string. string_agg in Postgres serves a similar purpose as group_concat in MySQL. If you're familiar with MySQL, you might have used the group_concat function. email, ‘, ’ )įROM customer INNER JOIN country ON customer. The need is to get all the customer emails grouped by country it can be done with a query that looks like the below: SELECT country.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |