-1

I am trying to create a view based on the following mySQL table:

+------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field                  | Type                | Null | Key | Default           | Extra                       |
+------------------------+---------------------+------+-----+-------------------+-----------------------------+
| id                     | bigint(20) unsigned | NO   | PRI | NULL              | auto_increment              |
| idPartner              | int(10) unsigned    | NO   | MUL | NULL              |                             |
| sBrand                 | varchar(512)        | YES  |     | NULL              |

I would like distinct id sBrand and idPartner values, calling something like this:

CREATE VIEW VBrand As select distinct sBrand, id, idPartner from tblProduct;

However, I need to also include a primary key to satisfy the retrieving API resource. Including 'id' w/ DISTINCT will not work.

My first thought is to query DISTINCT, then ALTER VIEW to tack on an id column, but apparently in order to edit a view you need to re-instantiate the original CREATE commands, thus rendering the whole effort pointless. All other solutions I have seen online (ie PARTITION, ORDER_BY) are for other versions of SQL.

Edit: I want to output unique values for partner and brand, ie:

+-------------------+-----------+
| sBrand            | idPartner |
+-------------------+-----------+
| PLANTERS          |     45933 |
| SARGENTO          |     45913 |
| TOTINOS           |     45933 |
| TRU MOO           |     45930 |
| VANITY FAIR       |     45913 |

But due to controller design, an id is expected. In effect I want to add id as an ancillary column to (distinct) sBrand and idPartner. However this is not possible in the DISTINCT query. Ideal output something like this:

+----------+----------+-----------+
| sBrand   | id       | idPartner |
+----------+----------+-----------+
| TOTIN    | 13774056 |     45933 |
| PLANTERS | 13774362 |     45913 |
| SARGENT  | 13774430 |     45933 |
| YOPL     | 13774764 |     45930 |
| APPL     | 13774768 |     45913 |
Nina
  • 79
  • 2
  • 10
  • Please provide sample data and desired results. And given that views don't have primary keys, please explain more clearly what is needed. – Gordon Linoff Dec 05 '20 at 19:36
  • see this question if this helps https://stackoverflow.com/questions/13566695/mysql-select-increment-counter – Avihay Tsayeg Dec 05 '20 at 19:41
  • 1
    @AvihayTsayeg When I implement something like suggested in that question answer, I get "View's SELECT contains a variable or parameter". This is turning out to be quite tricky given the limitations of view. – Nina Dec 05 '20 at 20:04
  • "are for other versions of SQL." This info is useless without saying which version you are using.... – Luuk Dec 05 '20 at 20:07
  • 2
    Do a GROUP BY, use MIN() or MAX() to pick one ID. – jarlh Dec 05 '20 at 20:08
  • Why are you creating a view? – Strawberry Dec 05 '20 at 23:44

1 Answers1

1

Do a GROUP BY, use MIN() (or MAX()) to pick one ID.

select sBrand, MIN(id) as id, idPartner
from tablename
group by sBrand, idPartner
jarlh
  • 42,561
  • 8
  • 45
  • 63