![]() ![]() As powerful as the WHERE clause is in SQL, we can make it even stronger when we apply SQL’s NOT, AND, and OR operators. For instance, a database administrator may wish to find every instance in a database where a user’s name is equal to “Ronnie” – or, more likely, not equal to “Ronnie”. Performance-wise also group by is better than distinct.In a previous tutorial, we learned how to work with the WHERE clause in SQL, which database developers can use when they wish to retrieve a group of records that meet a certain criteria or condition. Where having can be used to find duplicate values also.įunctionality-wise group by and distinct works similarly but group by also provide flexibility to use aggregate functions along with getting unique data. With GROUP BY and HAVING, you can select distinct values based on group of columns. FOR NO KEY UPDATE, FOR UPDATE, FOR SHARE and FOR KEY SHARE cannot be specified with DISTINCT: postgres=# select distinct(col1),col2,col3 from test order by 1 for update ĮRROR: FOR UPDATE is not allowed with DISTINCT clauseĪnother popular way to select non-duplicate values is by using the GROUP BY with HAVING clause. ![]() Or postgres=# select distinct(col1) from test group by distinct(col1) Ĥ. We also cannot use it in WHERE and GROUP BY conditions: postgres=# select * from test where distinct(col1)=1 In a SELECT query we cannot have more than one DISTINCT keyword: postgres=# select distinct 1, distinct 2 ģ. ![]() postgres=# create table distinct(n int) ĮRROR: syntax error at or near "distinct"Ģ. DISTINCT is a reserved keyword in PostgreSQL, so we cannot specify it as an object name. There are a few scenarios where distinct keyword cannot be used.ġ. You can use DISTINCT ON to display the first of each value in “col1”: postgres=# select distinct on (col1) col1,col2,col3 from test order by col1 When distinct cannot return unique row when all columns combination is not unique then we can use distinct on clause which will give first row from that set of duplicate rows.The column which we are specifying in DISTINCT ON should also be present in the ORDER BY clause otherwise you will get an error. Notice here that all rows have been displayed, duplicates included, as there are no distinct rows in the “col3” column.Ī variant of DISTINCT is DISTINCT ON. You can also use SELECT with DISTINCT on all columns of the table: postgres=# select distinct col1,col2,col3 from test order by 1 You can use SELECT with DISTINCT on two columns of the table: postgres=# select distinct col1,col2 from test order by 1 ĥ. DISTINCT can also be used with COUNT and other aggregates: postgres=# select col1,count(distinct(col1)) from test group by col1 Ĥ. SELECT with DISTINCT can also be used in an SQL inline query: postgres=# select col1 from (select distinct(col1) from test order by col1) as foo ģ. You can use SELECT with DISTINCT to find only the non-duplicate values from column “col1”: postgres=# select distinct(col1) from test order by col1 Ģ. Now that the table has been set up, let’s look at a few ways that DISTINCT can sort through the data:ġ. Use the SELECT query to see the rows of table “test”: postgres=# select * from test Insert a few records into table “test”: insert into test values (1,'abc','') Let’s set up a new table in PostgreSQL and use it to look at a few helpful ways that DISTINCT can remove duplicates and reveal useful information from the data.įirst, connect to psql terminal: /usr/pgsql-11/bin/psql -U postgres postgresĬreate a table, “test”: create table test(col1 int, col2 varchar,col3 date) Let's explore this keyword first.ĭISTINCT is used to remove duplicate rows from the SELECT query and only display one unique row from result set. One of the easiest ways to select distinct values is using the DISTINCT keyword. In this post, we are going to see how to select distinct values from SQL queries/statements. Keywords and clauses discussed with examples include: SUMMARY: This article reviews methods for selecting distinct values from query results in PostgreSQL. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |