Question 89057 in PostgreSQL Question posted in
The official documentation can be found here.

Get unique values for each column with LIMIT query using SQL – Postgresql

I have the table in the following format in PostgreSQL:

Name    Age Gender  Occupation
John    32  Male    Software Engineer
Sarah   26  Female  Graphic Designer
David   41  Male    Sales Manager
Emily   29  Female  Marketing Coordinator
Michael 37  Male    Project Manager

I would like to get all unique values from each column for the query result when also using limit. Is this achievable in postgresql?

So for example my query would be like this:

SELECT * FROM my_table
WHERE Gender = 'Male'
LIMIT 2;

I expect to get all unique values per column regardless of my limit:

Name: John, David, Michael
Age: 32, 41, 37
Occupation: Software Engineer, Sales Manager, Project Manager
Gender: Male

How can I do that using SQL?

Tags:

3

Answers


  1. 0

    You can use subqueries, to completely disregard outer where clause:

    SELECT
    (SELECT STRING_AGG (distinct Name,', ') FROM my_table) Name,
    (SELECT STRING_AGG (distinct Age,', ') FROM my_table) Age,
    (SELECT STRING_AGG (distinct Occupation,', ') FROM my_table) Occupation,
    gender
FROM
    my_table
WHERE 
    Gender = 'Male'
LIMIT 2;

    EDIT: To disregard only LIMIT you could use CTE.

    WITH t AS
(
    SELECT * 
    FROM my_table
    WHERE Gender = 'Male'
)
SELECT
    (SELECT STRING_AGG (distinct Name,', ') FROM t) Name,
    (SELECT STRING_AGG (distinct Age,', ') FROM t) Age,
    (SELECT STRING_AGG (distinct Occupation,', ') FROM t) Occupation,
    gender
FROM  
    t
LIMIT 2;
    Login or Signup to reply.
  2. 0

    You can use the following query to get the unique values for each column with the Limit,

    SELECT 
  (SELECT DISTINCT name FROM my_table LIMIT 1) AS name,
  (SELECT DISTINCT age FROM my_table LIMIT 1,1) AS age,
  (SELECT DISTINCT occupation FROM my_table LIMIT 2,1) AS occupation,
  ...
FROM my_table
LIMIT 1

    This query selects the distinct values for each column using subqueries with the DISTINCT keyword and assigns them to separate column aliases (column_1, column_2, column_3, etc.). The LIMIT keyword is used to specify the number of unique values to select for each column.

    Note that this query assumes that all columns in the table have the same data type. If columns have different data types.

    Additionally, this query may not be efficient for tables with a large number of columns or a large number of distinct values.

    Login or Signup to reply.
  3. 0

    Here it is using string_agg. You may use another aggregation function if more relevant.

    select
    string_agg(distinct "name", ',') "names",
    string_agg(distinct age::text, ',') ages,
    string_agg(distinct gender, ',') genders,
    string_agg(distinct occupation, ',') occupations
from the_table;

    See demo.

    Unrelated but it is not a good idea to keep age in a table because it keeps changing. Better have the date of birth and calculate the age using age function.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search