#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    3
    Rep Power
    0

    Grouping by showing wrong results


    I have the follow table structure...

    tbl_band_songs.id = Primary Ket Auto Inc
    tbl_band_songs.Artist = Artist Name
    tbl_band_songs.id3_artist = The Artists account ID

    If I run the following query...

    SELECT tbl_band_songs.id, tbl_band_songs.Artist,tbl_band_songs.id3_artist
    FROM tbl_band_songs
    ORDER BY tbl_band_songs.id DESC LIMIT 12

    This shows me the latest entries in the table and each artist has entered 3 songs each, however I want to group them by name "tbl_band_songs.Artist" only

    I tried the following...

    SELECT tbl_band_songs.id, tbl_band_songs.Artist,tbl_band_songs.id3_artist
    FROM tbl_band_songs
    GROUP BY tbl_band_songs.Artist
    ORDER BY tbl_band_songs.id DESC LIMIT 5

    But this is missing the last entry from the result.

    Would appreciate an insight in to where I am going wrong.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    First thing: your usage of group by is invalid SQL but MySQL chooses to return random data instead of rejecting the query.

    Please read these to articles to understand what is going on:


    Secondly you will need to be a bit more precise on what you want. What exactly are you trying to achieve?

    (And please learn how to use the [code] tags to format your SQL)
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    3
    Rep Power
    0
    Thank you Shammat, I will read your posted links.

    I am trying to display the names of the latest 5 artists to have made entries in the table.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    5
    Rep Power
    0
    Originally Posted by DJHankypark
    I am trying to display the names of the latest 5 artists to have made entries in the table.
    The rule of thumb with GROUP BY clauses is that any fields in the SELECT statement that do not appear in the clause must be within an aggregate function (sum, min/max, count, etc). The goal is to return one row a specific value in a table where many rows may exist. The use of aggregate functions helps display relevant information about that value (e.g. - the count() function shows how many rows with that value exist in the table).

    Since the goal of your query is to return the latest artists to appear in the table, you will want to utilize the ID field. There are two ways to go about it:

    Max() Function

    Code:
    SELECT artist, max(id) as id
    FROM tbl_band_songs
    GROUP BY artist
    ORDER BY max(id) DESC LIMIT 5
    This will return the 5 artists with the highest ID numbers in the table.

    Group Both

    Code:
    SELECT artist, id, count(*) as num_songs
    FROM tbl_band_songs
    GROUP BY artist, id
    ORDER BY id DESC LIMIT 5
    This should return the same result and by counting the number of rows you get the added bonus of counting the number of songs that artist has in the table (barring data errors).

    Let me know if you have any questions
    Last edited by LaughingMan33; January 6th, 2014 at 02:01 PM. Reason: formatting, code tags
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    3
    Rep Power
    0

    Thank you


    Excellent LaughingMan33 thank you for your help, sorry not replied sooner busy in work. Much appreciated

    Originally Posted by LaughingMan33
    The rule of thumb with GROUP BY clauses is that any fields in the SELECT statement that do not appear in the clause must be within an aggregate function (sum, min/max, count, etc). The goal is to return one row a specific value in a table where many rows may exist. The use of aggregate functions helps display relevant information about that value (e.g. - the count() function shows how many rows with that value exist in the table).

    Since the goal of your query is to return the latest artists to appear in the table, you will want to utilize the ID field. There are two ways to go about it:

    Max() Function

    Code:
    SELECT artist, max(id) as id
    FROM tbl_band_songs
    GROUP BY artist
    ORDER BY max(id) DESC LIMIT 5
    This will return the 5 artists with the highest ID numbers in the table.

    Group Both

    Code:
    SELECT artist, id, count(*) as num_songs
    FROM tbl_band_songs
    GROUP BY artist, id
    ORDER BY id DESC LIMIT 5
    This should return the same result and by counting the number of rows you get the added bonus of counting the number of songs that artist has in the table (barring data errors).

    Let me know if you have any questions

IMN logo majestic logo threadwatch logo seochat tools logo