ORDER BY id

Databases
1

I wrote down the query below, it works well by pulling that author first who wrote more jokes then other, who wrote less appears later in the list.

I created a pagination for the list of authors, and if the different authors have the same number of jokes then I got a problem. Some of the authors appear on the next page for the second time, while the other don’t appear at all.

I guess it suppose to be other parameter in the ORDER BY clause such as ->> (a.author_id) as default in the case of the same result of counting.


SELECT ja.author_id, COUNT(ja.jokes_id), a.author_id, a.author_name 
FROM " . TABLE_JOKES_AUTHORS . " ja, " . TABLE_AUTHORS . " a 
WHERE ja.author_id = a.author_id group by ja.author_id 
ORDER BY COUNT(ja.jokes_id) DESC;

Please tell me where I have to place (a.author_id)

Your help is greatly appreciated.

2

@ r937
Thank you so much, it helps me a lot , I completely understand your adjustments, but I thought that:


GROUP 
    BY a.author_id

suppse to be:


GROUP 
    BY ja.author_id

but it works the same - EXCELENT

@ ScallioXTX
You said: Why order by id? I would order by name, as that’s more natural.

You are RIGHT. I will do it the way you suggested.

Thank you guys.

Love SitePoint where you can always find someone’s hand! :slight_smile:

3

actually, it does not, although the difference is very subtle

first, let’s set up some test data so you can see how it works

CREATE TABLE test_authors
( author_id    INTEGER 
, author_name  VARCHAR(9)
);
INSERT INTO test_authors VALUES 
  ( 1, 'curly' )
, ( 2, 'larry' )
, ( 3, 'moe' )
, ( 4, 'shemp' )
, ( 5, 'joe' )
, ( 6, 'curly joe' )
;
CREATE TABLE test_jokes_authors
( joke_id   INTEGER 
, author_id INTEGER
);
INSERT INTO test_jokes_authors VALUES
 (  1 , 1 ) -- curly has 7 jokes
,(  2 , 1 ) -- curly has 7 jokes
,(  3 , 1 ) -- curly has 7 jokes
,(  4 , 1 ) -- curly has 7 jokes
,(  5 , 1 ) -- curly has 7 jokes
,(  6 , 1 ) -- curly has 7 jokes
,(  7 , 1 ) -- curly has 7 jokes
,(  8 , 2 ) -- larry has 3 jokes
,(  9 , 2 ) -- larry has 3 jokes
,( 10 , 2 ) -- larry has 3 jokes
,( 11 , 3 ) -- moe has 9 jokes
,( 12 , 3 ) -- moe has 9 jokes
,( 13 , 3 ) -- moe has 9 jokes
,( 14 , 3 ) -- moe has 9 jokes
,( 15 , 3 ) -- moe has 9 jokes 
,( 16 , 3 ) -- moe has 9 jokes
,( 17 , 3 ) -- moe has 9 jokes
,( 18 , 3 ) -- moe has 9 jokes
,( 19 , 3 ) -- moe has 9 jokes
;

so far, so good, yeah?

okay, now let’s try my query –

SELECT a.author_id
     , a.author_name 
     , COUNT(ja.joke_id) AS jokes
  FROM test_authors AS a 
LEFT OUTER
  JOIN test_jokes_authors AS ja
    ON ja.author_id = a.author_id 
GROUP 
    BY a.author_id 
ORDER 
    BY jokes DESC
     , a.author_id 

[COLOR="Blue"]author_id  author_name  jokes
    3      moe            9
    1      curly          7
    2      larry          3
    4      shemp          0
    5      joe            0
    6      curly joe      0[/COLOR]

everything appears as it should

now, let’s change the author_id as you suggested –

SELECT [COLOR="Red"]ja.author_id[/COLOR]
     , a.author_name 
     , COUNT(ja.joke_id) AS jokes
  FROM test_authors AS a 
LEFT OUTER
  JOIN test_jokes_authors AS ja
    ON ja.author_id = a.author_id 
GROUP 
    BY [COLOR="red"]ja.author_id[/COLOR] 
ORDER 
    BY jokes DESC
     , a.author_id 

[COLOR="Blue"]author_id  author_name  jokes
    3      moe            9
    1      curly          7
    2      larry          3[/COLOR]
[COLOR="red"] NULL      shemp          0[/COLOR]

see? there is a subtle difference – authors 4, 5, and 6 are not found in the right table, so your GROUP BY is on NULL for them – all the NULLs have been grouped into one row

however, the name is taken from one of them (at random), because it isn’t part of the GROUP BY

4

Why order by id? I would order by name, as that’s more natural.

Anyhow, which ever you pick, just put it at the end of the ORDER BY clause:

ORDER BY COUNT(ja.jokes_id) DESC, ja.author_id

A few more pointers if I may :slight_smile:

  1. Your table names suggest that there is a N:M relation between authors and jokes. If so, can a joke be written by multiple authors? If not, I guess you’d be better off making the relation 1:N
  2. Why do you select the author_id twice, once from every table? They are bound to be the same, so one of them is redundant
  3. I would rewrite it to an INNER JOIN:

SELECT
   ja.author_id
 , COUNT(ja.jokes_id)
 [s], a.author_id[/s]
 , a.author_name 
FROM " .
   TABLE_JOKES_AUTHORS . " ja
   INNER JOIN " .
   TABLE_AUTHORS . " a 
      ON
   ja.author_id = a.author_id
GROUP BY
  ja.author_id 
ORDER BY
   COUNT(ja.jokes_id) DESC 
 , author_id ASC
Edit:

Rudy beat me to it!
@Rudy: yes, an outer join is probably better than my inner join :slight_smile:

5 
SELECT a.author_id
     , a.author_name 
     , COUNT(ja.jokes_id) AS jokes
  FROM " . TABLE_AUTHORS . " AS a 
LEFT OUTER
  JOIN " . TABLE_JOKES_AUTHORS . " AS ja
    ON ja.author_id = a.author_id 
GROUP 
    BY a.author_id 
[COLOR="Blue"]ORDER 
    BY jokes DESC
     , a.author_id [/COLOR]

i also made several other useful changes to your query, please ask if you don’t understand them

:slight_smile: