MySQL Forums
Forum List  »  Newbie

Group certain columns then chose a First/Last entry in one of the columns
Posted by: MILOS PROKIC
Date: August 18, 2017 10:55AM

Hi! I'm quite new to MySQL, but I have some experience with MS Access.

I am looking to perform a Select statement within a View with a Group By option on 3 columns. On the 4th column, I'm looking to take the FIRST or LAST occurrence.

The Select statement is not run on a table, rather it is run on a View.

The initial View produces 4 columns:
|Company|Year|Product|OrgUnit|Sales| they are ordered so that the OrgUnit selling the most of a product is showing first.
My goal - come up with a View that features
|Company|Year|Product|OrgUnit| - where I will have only 1 OrgUnit listed - one that sells the most of a product.

What I have tried so far is to create a MySql function in hopes that I can add a unique, auto-incremented ID to the view which will allow me to then group and pick the MAX/MIN id and deduce which row I should consider.

The function that I tried is:
CREATE DEFINER=`milos`@`%` FUNCTION `func_inc`() RETURNS int(11)
NO SQL
begin SET @var := IFNULL(@var,0) + 1; return @var; end

However, I can't or I don't know how to initialize the variable so that it always starts at 1, every time the View is run.

Anyway, in MS Access I would've grouped by 3 columns and picked First/Last on OrgUnit, but somehow this seemingly simple task is taking me too long on MySql. Hope someone can help me out.

Thank you!

Options: ReplyQuote


Subject
Written By
Posted
Group certain columns then chose a First/Last entry in one of the columns
August 18, 2017 10:55AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.