www.webdeveloper.com
Results 1 to 10 of 10

Thread: Group by one column, with max value of other column

  1. #1
    Join Date
    Oct 2007
    Location
    Sweden
    Posts
    225

    Group by one column, with max value of other column

    I bet it's not as tricky as it sounds, but I cant figure it out.

    Let's say we've got a table with columns
    Fruit, Quality

    And they contain something like
    Apple, 5
    Banana, 4
    Banana, 3
    Apple, 6
    Orange, 4

    How would one take out a list with one of each fruit with maximum quality value? (Never mind order)
    Apple, 6
    Banana, 4
    Orange, 4

    I've tried using Group by and MAX(), but since MAX() only returns one row it's kinda useless.

    Any help is appreciated. (And yes, I suck at SQL)

  2. #2
    Join Date
    Mar 2010
    Posts
    2,803
    This should do the trick:

    Code:
    select fldFruit, max(fldQuality) 
    from tblfruit
    group by fldFruit;
    I'm not sure how you used the group by and max() in your query, but you have to use them together as above to get the result you wanted.
    Last edited by tirna; 04-13-2010 at 06:30 AM.

  3. #3
    Join Date
    Oct 2007
    Location
    Sweden
    Posts
    225
    Aye, however, if we add an ID to the fruit, it gets mixed up.

    Fruit, Quality, ID
    Apple, 5, 1
    Banana, 4, 2
    Banana, 3, 3
    Apple, 6, 4
    Orange, 4, 5

    Becomes
    Apple, 6, 1
    Banana, 4, 2
    Orange, 4, 5

    It selects the ID from the first mentioned fruit, but the maximum quality.
    Can one get around it so it selects all the columns from the fruit with max quality?

  4. #4
    Join Date
    Mar 2010
    Posts
    2,803
    What you are saying doesn't make sense to me.

    Any extra columns in the table should not affect the results returned by the query I posted.

    Maybe do a
    Code:
    select * from tblfruit
    and post a few rows of the result so we can see exactly what your table looks like.

  5. #5
    Join Date
    Oct 2007
    Location
    Sweden
    Posts
    225
    Alright, I have the `vhFiles` table:
    FileID, Path, Data, Version, Filesize

    And I have the `vhVersions` table:
    Version, Date, Published

    The first table can contain multiple files with the same Path, but different version (and data).
    I want to select all files, but only one occurrence of each Path with the highest version available, where the version is published in the other table (Published = 1)

    I've tried this:
    Code:
    SELECT Path, Version, FileID, MAX(Version) MaxV
    FROM `vhFiles`
    INNER JOIN `vhVersions`
    USING(Version)
    WHERE Pubished='1'
    GROUP BY Path
    ORDER BY Version DESC, FileID ASC
    However, this doesn't select the Data or FileID from the MAX(Version).
    Example:
    Code:
    FileID, Path, Version
    1, license.txt, 1.0.2
    4, license.txt, 1.0.3
    
    Result:
    FileID, Path, Version, MaxVersion
    1, license.txt, 1.0.2, 1.0.3
    I want to select the whole row from where he found the MAX(Version), not only the MAX(Version) value.
    Last edited by Quidam; 04-13-2010 at 07:30 AM.

  6. #6
    Join Date
    Mar 2010
    Posts
    2,803
    Quote Originally Posted by Quidam View Post
    I want to select the whole row from where he found the MAX(Version), not only the MAX(Version) value.
    Now you are changing what you want from what you said you wanted in your first post.

    In your first post, your example had only 2 columns. You didn't mention the table actually has extra columns and you want every column returned where a max value is found.

    You should have mentioned exactly what you want in your first post.

  7. #7
    Join Date
    Oct 2007
    Location
    Sweden
    Posts
    225
    Yeah, sorry 'bout that.
    I'm not really great at SQL, I thought that I could make it out if I just got a little push in the right direction.
    I also thought that if I got the max value out of it I could get the whole row, but that didn't happen.

    Hope that someone can help me with this one though, I would really appreciate it.

  8. #8
    Join Date
    Mar 2010
    Posts
    2,803
    I'm still not exactly sure what you want, but from my understanding then hoprfully this will be close to what you need.

    Code:
     
    select FileID, Path, Data, f.Version, Filesize
    from vhFiles f, vhVersions v
    where f.Version = v.Version
    and v.Published = 1
    and f.Version in 
    (select max(Version) from vhFiles group by Path);
    If you can't or are not allowed to post at least a few rows of the data from each table like I asked and an example of the output listing you want using the actual data from the tables, then I can't help much more.

    Hopefully someone else can help more.

  9. #9
    Join Date
    Oct 2007
    Location
    Sweden
    Posts
    225
    Thanks for your help, really appreciate it.

    That query gave me the same result as this one, except that it had longer execution time (added the order by on that one as well)
    Code:
    SELECT FileID, Path, Data, Version, Filesize
    FROM vhFiles
    INNER JOIN vhVersions
    USING (Version)
    WHERE Publish='1'
    ORDER BY Version
    Top 5 rows with those queries (result is the same)
    Code:
    FileID	Path		Data	Version		Filesize
    762	man.pdf		[BLOB]	1.0.3		423061
    763	inst.pdf	[BLOB]	1.0.3		9234
    1	rdme.txt	[BLOB]	1.0.2		311
    2	man.pdf		[BLOB]	1.0.2		423061
    3	inst.pdf	[BLOB]	1.0.2		9234
    That is how the original vhFiles table look like as well, only difference is that we only see the files of published versions.

    What I would like is to make those two last go away (FileID 2 and 3), since I have newer versions of them (1.0.3).

    What I want, top 5
    Code:
    FileID	Path		Data	Version		Filesize
    762	man.pdf		[BLOB]	1.0.3		423061
    763	inst.pdf	[BLOB]	1.0.3		9234
    1	rdme.txt	[BLOB]	1.0.2		311
    4	index.php	[BLOB]	1.0.2		1782
    5	script.js	[BLOB]	1.0.2		3892
    I hope this makes any sense

  10. #10
    Join Date
    Jan 2014
    Posts
    1
    Hope this makes sence
    Code:
    SELECT *
    FROM ( SELECT *
           FROM ( SELECT 'Apple' as fruit, 5 as quality, 1 as id
                  UNION
                  SELECT 'Banana', 4, 2
                  UNION
                  SELECT 'Banana', 3, 3
                  UNION
                  SELECT 'Apple', 6, 4
                  UNION
                  SELECT 'Orange', 4, 5
                ) as fruits
           ORDER BY fruit, quality DESC
         ) as fruits
    GROUP BY fruit

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles