Photoshop Question posted in
The official user guide can be found here.

Obtain distinct row and * from one table – Photoshop

I’m trying obtain one tabla of cars which have three rows in relations with other three columns (make, model and group) and I only want obtain one car by model.

Here a image of MySQL table:
enter image description here

You will see three rows with same model_id (model_id is foreign key the other table, the other table is called models)

My SQL query for obtain those cars are:

SELECT *
FROM gm_cars AS cars
INNER JOIN gm_cars_makes AS makes
   ON (cars.make_id = makes.make_id)
INNER JOIN gm_cars_models AS models
   ON (cars.model_id = models.model_id)
INNER JOIN gm_cars_groups AS groups
   ON (cars.group_id = groups.group_id) AND
       makes.make_visible = 1
ORDER BY cars.model_id;

but I wish obtain one row for one model, here one example (I have used Photoshop):
enter image description here

Some like: SELECT *, DISTINCT(model_id) FROM cars

Tags:

3

Answers


  1. 0

    If you still want to return all columns, you can create sub-query to return only one Car per Model and then write you query as before:

    SELECT * FROM gm_cars AS cars 
INNER JOIN (SELECT model_id, MAX(car_Id) AS car_Id FROM gm_cars GROUP BY model_id) AS grp_cars ON grp_cars.car_Id = cars.car_Id
INNER JOIN gm_cars_makes AS makes ON (cars.make_id = makes.make_id) 
INNER JOIN gm_cars_models AS models ON (cars.model_id = models.model_id) 
INNER JOIN gm_cars_groups AS groups ON (cars.group_id = groups.group_id) AND makes.make_visible = 1 ORDER BY cars.model_id;

    You can also add GROUP BY in you main query, and add aggregation function to all other columns. But it can return you columns from different cars with the same model:

    SELECT cars.model_id,
    MAX(car_passengers),
    MAX(car_suitcases),
    ....
FROM gm_cars AS cars 
INNER JOIN (SELECT model_id, MAX(car_Id) AS car_Id FROM gm_cars GROUP BY model_id) AS grp_cars ON grp_cars.car_Id = cars.car_Id
INNER JOIN gm_cars_makes AS makes ON (cars.make_id = makes.make_id) 
INNER JOIN gm_cars_models AS models ON (cars.model_id = models.model_id) 
INNER JOIN gm_cars_groups AS groups ON (cars.group_id = groups.group_id) AND makes.make_visible = 1 
GROUP BY cars.model_id
ORDER BY cars.model_id;
    Login or Signup to reply.
  2. 0 
    SELECT * 
FROM gm_cars AS cars 
INNER JOIN gm_cars_makes AS makes ON (cars.make_id = makes.make_id) 
INNER JOIN gm_cars_models AS models ON (cars.model_id = models.model_id) 
INNER JOIN gm_cars_groups AS groups ON (cars.group_id = groups.group_id) 
AND makes.make_visible = 1 
group by (model_id)
ORDER BY cars.model_id
    Login or Signup to reply.
  3. 0

    is this helpful?

        ;with cte 
    AS
    (
        Select *,row_number() OVER(partition by model_id order by car id) rn from gm_cars 
    )

    select * from cte where rn=1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search