Postgresql – SELECT DISTINCT with another table

postgresql

I want to use distinct to display my data.
But I don't know how to do it.This is my code.I want to display table gtest_version field max(poly_version)

select distinct on (rule_name) 
rule_name,rule_version,polyname,state 
from rule 
order by rule_name,rule_version DESC

enter image description here

Best Answer

SELECT distinct on (rule_name) 
  rule.rule_name,rule.rule_version,rule.polyname,rule.state, g.poly_version 
FROM  rule INNER JOIN gtest_version As g ON rule.polyname = g.polyname 
ORDER BY rule_name,g.poly_version DESC, rule_version DESC;

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

You can try something like

SELECT field1, field2 
FROM table1 
ORDER BY ctid
OFFSET x
LIMIT 100
;

As I commented, without ordering you can never be sure that you get the same 100 rows. This way you will get the same rows in a certain order if they don't get updated or deleted.

Postgresql – How to create an Sql table from SELECT with a multiple row DISTINCT constraint

distinct is NOT a function. It always operates on all columns of the result.

The difference between select distinct (foo), bar and select distinct foo, bar is the same as the difference between select (foo), bar and select foo, bar. The parenthesis is just "noise".

When you write select (foo,bar) you are actually creating an anonymous record type in Postgres which results in a single column that has two attributes - which is not what you actually want.

As you are using Postgres, you can use the (proprietary) operator DISTINCT ON which - in contrast to the standard DISTINCT - does operate on a sub-set of the columns.

You have to specify an ORDER BY in that case to define which of the rows to take if there is more than one with the same combination of (field1, field2).

CREATE TABLE new_name 
AS 
SELECT DISTINCT ON (table.field1, table.field2), 
       table.field1, 
       table.field2, 
       .....
FROM ...
WHERE ...
ORDER BY ..

If you want to stick to ANSI SQL you will need a window function for this:

create table new_name
as
select column1, column2, column3, column4, column5, column6
from (
   select column1, column2, column3, column4, column5, column6, 
          row_number() over (partition by column1, column2 order by ...) as rn 
   from the_table
   where ...
) t
where rn = 1;

For a large table, DISTINCT ON is probably faster than the solution with the window function.

Best Answer

Related Solutions

Postgresql – Select consecutive number of rows starting from some number

Postgresql – How to create an Sql table from SELECT with a multiple row DISTINCT constraint

Related Question