CODEWITHSUNDEEP
pythonmysqlsqlalchemyormflask-sqlalchemy
Glen Veigas
Glen Veigas

Reputation: 75

How to get distinct values from many columns of table using sqlAlchemy ORM?

I have created a database with these tables in it

Employee(id, name, deptid, salary)
Department(id, name)

with these values in it

Employee
id     | name       | deptid     | salary.   |
------- ------------ ------------ -----------
1        Alex           1            600000
2        Larry          1            700000
3        Jesse          3            400000
4        Alex           2            500000
5        Marcus         3            400000

Department
id      | name      |
-------- ----------- 
1.       Engineering
2.       Finance
3.       Sales

I want to fetch the distinct values from every column of the database I want the output to look something like this

name    
------
Alex
Larry
Jesse
Marcus

deptid
-------
1
3
2

I have written a ORM query like this

session.query(Employee.name, Employee.deptid).distinct().all()

this returns me

[('Alex',1,), ('Larry',1), ('Jesse', 3),('Alex',2),('Marcus',3)]

this is returning distinct values of both columns combined, which is not the desired solution I am looking for rather I am looking for rather I want the solution with distinct values of different columns in one query itself.

Upvotes: 0

Views: 947

Answers (1)

ljmc
ljmc

Reputation: 5264

SELECT DISTINCT is applied to the full rows that are being selected.

You will therefore need two SELECT DISTINCT queries to find the distinct values in two different columns.

session.query(Employee.name).distinct().all()

session.query(Employee.deptid).distinct().all()

You should also use Department.id directly to find its distinct values, rather than using the foreign key Employee.deptid, unless you only want to find the referenced ones.

Upvotes: 1

Related Questions