Re: order by + group by

[Peter of the Norse]

On Sep 14, 2011, at 7:19 AM, Jani Tiainen wrote:

> 14.9.2011 12:46, Jonas H. kirjoitti:
>> On 09/14/2011 11:37 AM, Иван Иванов wrote:
>>> The problem here is, like Peter said, that you cannot order before
>>> grouping. And that's very annoying.
>> 
>> Of course you can, using a subselect just like I showed. SQLite example:
>> 
>> > .schema
>> CREATE TABLE a (name varchar, mod int);
>> 
>> e> select * from a;
>> b1|1
>> b1|2
>> b1|3
>> 
>> > select * from a group by name;
>> b1|3
>> 
>> > select * from (select * from a order by mod desc) group by name;
>> b1|1
>> 
> 
> Problem is that is not standard SQL.
> 
> In standard implemetation group by _requires_ aggregation function that is 
> applied to groups so you won't be able to get "last" that way.
> 
> It might work non-standard way in some implementations. And thus Django 
> usually follows standard or smallest common nominator for db backends such a 
> thing is not possible to support.

I didn’t know you could do that in SQLite. I know it doesn’t work in MySQL or 
in PostgreSQL.

The proper ANSI SQL way of doing this is “SELECT name, MIN(mod) FROM a GROUP BY 
name” and the Django way is 
“Model.objects.values('name').annotate(Min('mod'))”. But that only works on a 
table with two fields. If there is more data that you want to include, you’ll 
have to get raw. The SQL will look like “SELECT * FROM a AS t1 WHERE mod = 
(SELECT MIN(mod) FROM a AS t2 WHERE t2.name = t1.name))”. Finding out the exact 
details depends on the specifics of your queryset.

https://docs.djangoproject.com/en/1.3/topics/db/aggregation/

Peter of the Norse
rahmc...@radio1190.org



-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: order by + group by

[Jani Tiainen]

14.9.2011 12:46, Jonas H. kirjoitti:

On 09/14/2011 11:37 AM, Иван Иванов wrote:

The problem here is, like Peter said, that you cannot order before
grouping. And that's very annoying.


Of course you can, using a subselect just like I showed. SQLite example:

 > .schema
CREATE TABLE a (name varchar, mod int);

e> select * from a;
b1|1
b1|2
b1|3

 > select * from a group by name;
b1|3

 > select * from (select * from a order by mod desc) group by name;
b1|1



Problem is that is not standard SQL.

In standard implemetation group by _requires_ aggregation function that 
is applied to groups so you won't be able to get "last" that way.


It might work non-standard way in some implementations. And thus Django 
usually follows standard or smallest common nominator for db backends 
such a thing is not possible to support.


--

Jani Tiainen

--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: order by + group by

[Jonas H.]

On 09/14/2011 11:37 AM, Иван Иванов wrote:

The problem here is, like Peter said, that you cannot order before
grouping. And that's very annoying.


Of course you can, using a subselect just like I showed. SQLite example:

> .schema
CREATE TABLE a (name varchar, mod int);

e> select * from a;
b1|1
b1|2
b1|3

> select * from a group by name;
b1|3

> select * from (select * from a order by mod desc) group by name;
b1|1

--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: order by + group by

[Иван Иванов]

На Wed, 14 Sep 2011 10:59:01 +0200
"Jonas H."  написа:

> On 09/14/2011 04:35 AM, Peter of the Norse wrote:
> > What are you trying to do? The outer GROUP BY destroys the inner
> > ORDER BY. Try putting them on the same level and see what happens.
> 
> Say I've got an Event table and some records like this
> 
>name: foo, timestamp: 9 pm
>name: foo, timestamp: 10pm
>name: bar, timestamp: 3pm
> 
> I want the latest event for every `name`:
> 
>name: foo, timestamp: 10pm
>name: bar, timestamp: 3pm
> 
> On PostgreSQL one could use DISTINCT ON but it's not yet supported by 
> Django, either.


I've done something like this on MySQL before several weeks. 

The problem here is, like Peter said, that you cannot order before
grouping. And that's very annoying.

I haven't found a way to do it without raw sql. Here is my approach:


cursor = connection.cursor()
cursor.execute("CREATE TEMPORARY TABLE `tmp` LIKE `your_table_name`;")
cursor.execute("ALTER TABLE `tmp` ADD UNIQUE KEY `uniq_name` (`name`);")
cursor.execute("INSERT IGNORE INTO `tmp` (SELECT * FROM `your_table_name` 
WHERE time<=%s ORDER BY `time` DESC);", [mydate])
transaction.commit_unless_managed()
cursor.execute("SELECT name, time FROM tmp;")
result = cursor.fetchall()

Explained:
First we create temporary table, which copies the structure of your table, 
where you've got the timestamps.
Then we add unique key, which is the column, you want to group by.
Then you make an insert ignore statement with nested select, 
where you make your ordering by timestamp.
Here I've got where clause too, where I want only theese rows, 
before specified time. I don't know if you need it.

And at the end you just select all rows from the tmp table, and they are all 
rows you actually need.

In Django, every request is new sql connection, so you don't need to worry 
about the livetime of the tmp table.

That's it.

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: order by + group by

[Jonas H.]

On 09/14/2011 04:35 AM, Peter of the Norse wrote:

What are you trying to do? The outer GROUP BY destroys the inner ORDER BY. Try 
putting them on the same level and see what happens.


Say I've got an Event table and some records like this

  name: foo, timestamp: 9 pm
  name: foo, timestamp: 10pm
  name: bar, timestamp: 3pm

I want the latest event for every `name`:

  name: foo, timestamp: 10pm
  name: bar, timestamp: 3pm

On PostgreSQL one could use DISTINCT ON but it's not yet supported by 
Django, either.


--

A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?

--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: order by + group by

[Peter of the Norse]

What are you trying to do? The outer GROUP BY destroys the inner ORDER BY. Try 
putting them on the same level and see what happens.

On Sep 11, 2011, at 3:41 AM, Jonas H. wrote:

> Hi!
> 
> How can I express this SQL query
> 
>  SELECT ... FROM (
>SELECT ... FROM ...
>  )
> 
> in the ORM? Specifically, I want to express
> 
>  SELECT ... FROM (
>SELECT ... FROM app_model
>ORDER BY field1
>  )
>  GROUP BY field2
> 
> Is it possible without writing SQL by hand?
> 
> Thanks,
> Jonas

Peter of the Norse
rahmc...@radio1190.org



-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

order by + group by

[Jonas H.]

Hi!

How can I express this SQL query

  SELECT ... FROM (
SELECT ... FROM ...
  )

in the ORM? Specifically, I want to express

  SELECT ... FROM (
SELECT ... FROM app_model
ORDER BY field1
  )
  GROUP BY field2

Is it possible without writing SQL by hand?

Thanks,
Jonas

--
Django + MongoDB: http://django-mongodb.org

--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.