Other [SOLVED] SQL general question... SELECT/GROUP/ORDER

Magma · Sep 7, 2017

SELECT * FROM (select * from exampletable where name in ('george,'nick','gregory') order by id DESC) as t group BY name LIMIT 3;

So this return to me three unique and specific... but gets firsts id (or what sql finds more faster) ... not in mind the order by id...

anyone can help for ORDER BY AND GROUP together ???

thanks in advance

Widget · Sep 7, 2017

"I want to get three last values and only from unique name values (and more specific for george,nick,gregory) order by DESC;..."
It is still not clear how you determine the "three last values".
1) Do you want the 3 people who have the lowest accumulated points?
2) Or the 3 people who have the highest accumulated points?
3) Or something else?

imbault · Sep 7, 2017

Widget said:
"I want to get three last values and only from unique name values (and more specific for george,nick,gregory) order by DESC;..."
It is still not clear how you determine the "three last values".
1) Do you want the 3 people who have the lowest accumulated points?
2) Or the 3 people who have the highest accumulated points?
3) Or something else?

I agree, be more specific, what do you want to get?

mc73 · Sep 7, 2017

Perhaps:

B4X:

SELECT  * from exampletable where name in ('george','nick','gregory') group by name order by id desc

OliverA · Sep 7, 2017

B4X:

SELECT * FROM (select * from exampletable where name in ('george,'nick','gregory') order by id DESC) as t group BY name LIMIT 3;

1) Both @Widget's and @imbault's questions have merit

2) When using GROUP BY, each column in the SELECT portion of the statement should(MySQL/MariaDB) / must (other DB's) be referenced either in the GROUP BY clause or needs to be used with a AGGREGATE function. Most databases will throw and error message if this is not done. It looks like MySQL/MariaDB will not. Please note that this will make results unpredictable. Here's a quote from a quote from a stackoverflow post answer:

When using this feature, all rows in each group should have the same values
for the columns that are omitted from the GROUP BY part. The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same.

Here's a post showing some of the error messages Oracle will throw: https://stackoverflow.com/questions/20074562/group-by-without-aggregate-function

3) The ORDER BY in the sub-query may not be doing what your are trying to do (or what I read into what you are trying to do). The MariaDB documentation states that sub-query ORDER BY clauses are ignored by the query calling the sub-query. The ORDER BY needs to be at the top-level SELECT statement. See MariaDB's documentation on this subject: https://mariadb.com/kb/en/the-mariadb-library/why-is-order-by-in-a-from-subquery-ignored/. BTW, this should apply to any SQL DB.

To summarize (points 1&2): Your SQL statement is relying on two non-determining, unpredictable result giving methods (ORDER BY in a sub-query and GROUP BY without proper aggregation) to retrieve a result that is going to end up not giving you what you expect.

@mc73 : Your statement will still suffer from GROUP BY issue given in point #2 of this post.

Magma · Sep 8, 2017

@Widget (3) something else... i want 3 specific people george,gregory,nick ---> getting their last id's....

@mc73 already tried... have the same results... :-(

@OliverA I am agree with u - for this: The server is free
to return any value from the group, so the results are indeterminate unless
all values are the same. - I AM SUFFERING from this...

So what to do ? can you give an example at my... problem... or it can;t have solution with one sql line ?

Magma · Sep 8, 2017

...oops...

found solution / ofcourse with help of all you guys - speciallly from OLIVERA...

B4X:

select max(id),name,points from exampletable where name in ('gregory','george','nick') group by name;

it was easy... but thinking was difficult

Mahares · Sep 8, 2017

Magma said:
it was easy... but thinking was difficult

If you mentioned in your first post that the result you want to achieve is the following based on the table you displayed, I am sure someone would have given you the correct answer right away and users would not have to guess at what your question was. You confused all of us with your question.
7,george,5
2,gregory,10
6,nick,10

OliverA · Sep 8, 2017

Magma said:

B4X:

select max(id),name,points from exampletable where name in ('gregory','george','nick') group by name;

Please note that this query still suffers from GROUP BY issue I pointed out, since "points" has no aggregation function and therefore can return anything. Your data set may be small enough where it looks like it works, but with a larger data set you should start seeing issues. So here we go...

B4X:

SELECT exampletable.id, name ,points FROM exampletable
INNER JOIN (select max(id) AS id
            FROM exampletable
            WHERE name IN ('gregory','george','nick') 
            GROUP BY name) AS t
ON t.id = exampletable.id;

This is with the assumption that the "id" field in exampletable is unique, otherwise I would construct the query as follows

B4X:

SELECT exampletable.id, exampletable.name ,points FROM exampletable
INNER JOIN (select max(id) AS id, name 
            FROM exampletable
            WHERE name IN ('gregory','george','nick') 
            GROUP BY name) AS t
ON t.name = exampletable.name AND t.id = exampletable.id

Magma · Sep 8, 2017

Mahares said:
If you mentioned in your first post that the result you want to achieve is the following based on the table you displayed, I am sure someone would have given you the correct answer right away and users would not have to guess at what your question was. You confused all of us with your question.
7,george,5
2,gregory,10
6,nick,10

I am sure after explanations that all users understand - what exact is the problem now ?

Magma · Sep 8, 2017

OliverA said:
Please note that this query still suffers from GROUP BY issue I pointed out, since "points" has no aggregation function and therefore can return anything. Your data set may be small enough where it looks like it works, but with a larger data set you should start seeing issues. So here we go...

B4X:

SELECT exampletable.id, name ,points FROM exampletable INNER JOIN (select max(id) AS id FROM exampletable WHERE name IN ('gregory','george','nick') GROUP BY name) AS t ON t.id = exampletable.id;

This is with the assumption that the "id" field in exampletable is unique, otherwise I would construct the query as follows

B4X:

SELECT exampletable.id, exampletable.name ,points FROM exampletable INNER JOIN (select max(id) AS id, name FROM exampletable WHERE name IN ('gregory','george','nick') GROUP BY name) AS t ON t.name = exampletable.name AND t.id = exampletable.id

So the first solution you giving is better if id is unique (it is)... and faster with no problem than the solution of max(id) (still there id is unique)..

What you suggest to use ?

eps · Sep 8, 2017

So....

You want to get the summed totals for 3 specific persons?

i.e.

george 30
gregory 10
nick 30

You're not interested in nataly...

Magma · Sep 8, 2017

i want 3 specific people george,gregory,nick ---> getting their last id's....

OliverA · Sep 8, 2017

Magma said:
So the first solution you giving is better if id is unique (it is)... and faster with no problem than the solution of max(id) (still there id is unique)..

What you suggest to use ?

B4X:

SELECT exampletable.id, name ,points FROM exampletable
INNER JOIN (select max(id) AS id
            FROM exampletable
            WHERE name IN ('gregory','george','nick')
            GROUP BY name) AS t
ON t.id = exampletable.id;

Other [SOLVED] SQL general question... SELECT/GROUP/ORDER

Magma

Expert

Widget

Well-Known Member

imbault

Well-Known Member

mc73

Well-Known Member

OliverA

Expert

Magma

Expert

Magma

Expert

Mahares

Expert

OliverA

Expert

Magma

Expert

Magma

Expert

eps

Expert

Magma

Expert

OliverA

Expert

Similar Threads

Other [SOLVED] SQL general question... SELECT/GROUP/ORDER

Expert

Well-Known Member

Well-Known Member

Well-Known Member

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency