order by before group by in sqlalchemy

Question

want to get last 2 name in my table for example

id    name    price
1     name1   100
2     name2   120
3     name3   100
4     name3   150
5     name3   152

i want result become

name
name3
name2

already searched similar questions here but find no answer in SQLalchemy

my try

orders = db.session.query(Table.name).filter(SomeFilter).order_by(Table.id.desc()).group_by(
        Table.name).all()

problem is group_by and order_by in same time have no issue with limit

EDIT2

result = session.query(test).order_by(test.i.desc()).with_entities(test.j).distinct(test.j).limit(2)

get error in postgress too

use `limit()`, see: [Sqlalchemy get last X rows in order](https://stackoverflow.com/questions/12345015/sqlalchemy-get-last-x-rows-in-order) — Luuk, Dec 04 '21 at 10:04
want ```group_by``` and ```limit``` if use ```group_by``` ```limit``` not work correctly — Nozar Safari, Dec 04 '21 at 10:07
problem is ```order by``` after ```group by``` will sort on aliphatic order i want to sort on updated time — Nozar Safari, Dec 04 '21 at 13:38

Luuk · Answer 1 · 2021-12-05T10:46:20.227

OK, I am (very) new to sqlalchemy (so do not mention the 'lot of' imports at the start of this code ), but see my code:


import sqlalchemy

from sqlalchemy import create_engine, Column, String, Integer, MetaData, Table
from sqlalchemy import text
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("sqlite+pysqlite:///test.db", echo=True)

Base = declarative_base()
metadata = MetaData(engine)
class test(Base):
    __tablename__ = 'test'
    i = Column(Integer,primary_key=True)
    j = Column(Integer)

with Session(engine) as session:
    session.execute("""CREATE TABLE test(i int primary key,j int)""")
    session.execute("""DELETE FROM test""")
    t1 = test(i=1, j=1)
    session.add(t1)
    session.add(test(i=2,j=5))
    session.add(test(i=3,j=5))
    session.add(test(i=4,j=2))
    session.add(test(i=5,j=2))
    session.add(test(i=6,j=3))
    session.add(test(i=7,j=3))
    session.add(test(i=8,j=3))

    session.commit()
    result = session.query(test).group_by(test.j).order_by(test.j.desc()).limit(2)
    for row in result:
          print(f"I: {row.i}  J: {row.j}")

BTW: The DELETE FROM... is only needed on second run, when I comment the line with CREATE TABLE....

the output is:

I: 6  J: 3
I: 4  J: 2

EDIT: The items should be sorted on i desc. To get that done you can use the following code:

(NOTE: j=1 is changed to j=5, to get the correct result)

    subquery = session.query(test).order_by(test.i.desc()).with_entities(test.j).subquery()
    result = session.query(subquery).distinct().limit(2)
    for row in result:
          print(f"J: {row.j}")

which, with my sample data, returns:

J: 3
J: 2

EDIT2: Better result always arise after posting the code here....

It can be done without subquery:

result = session.query(test).order_by(test.i.desc()).with_entities(test.j).distinct(test.j).limit(2)

EDIT3: It appears the postgresql is giving an error (see comments).

the following changes are needed to solve this (with help from PG::Error: SELECT DISTINCT, ORDER BY expressions must appear in select list:

add from sqlalchemy import func
change create engine(sqlite... to create_engine("postgresql://user:password@localhost/dbname", echo=True,)
finally the selection looks like:

    result = session.query(test).order_by(func.min(test.i).desc()).with_entities(test.j).group_by(test.j).limit(2)
    for row in result:
          print(f"J: {row.j}")

order by before group by in sqlalchemy

1 Answers1