select first three values order by a column

Question

i have 2 tables :

med

CREATE TABLE med 
(id_med INT AUTO_INCREMENT PRIMARY KEY,
name_m VARCHAR(50),
surname_m VARCHAR(50),
spec VARCHAR(50),
UNIQUE (surname_m,name_m));

viz

CREATE TABLE viz
(id_m INT NOT NULL,
id_p INT  NOT NULL,
id_c INT  NOT NULL,
dt DATETIME)

i need to select the first 3 values for the fullname and count(id_p) for each spec. the following query gives me the first value but i just cant see what to do next.

  (SELECT distinct(concat(name_m,' ',surname_m)) AS fullname, COUNT(id_p) as no, (select distinct(spec))
    FROM med m JOIN viz v ON m.id_med = v.id_m
    WHERE year(dt)=2005
    GROUP BY spec
    ORDER BY spec;

any answer will be highly appreciated. thank you.

the result should be like this :

  fullname       count(id_p)          spec
    name1              1000            a
    name2               900            a
    name3               890            a
    name4              2000            b
    name5               600            b
    name6               200            b
    name7               100            c
    name8                60            c
    name9                59            c

....

my current result shows:

    fullname       count(id_p)        spec
    name1              1000            a
    name4              2000            b
    name7               100            c

Can you fill in the tables with data and give an example of what you want your query to return. — Daniel A. Thompson, Jun 24 '16 at 00:34
so now what is your result is showing according to your current query? — Hamza Zafeer, Jun 24 '16 at 01:07

Barmar · Answer 1 · 2016-06-24T14:27:04.050

0

Join with a subquery that returns the first three values of the spec column.

SELECT DISTINCT CONCAT(name_m, ' ', 'surname_m) AS fullname, COUNT(id_p) AS no, m1.spec
FROM med AS m1
JOIN viz AS v ON m1.id_med = v.id_m
JOIN (SELECT DISTINCT spec
      FROM med
      ORDER BY spec
      LIMIT 3) AS m2 ON m1.spec = m2.spec
WHERE YEAR(v1.dt) = 2005
GROUP BY fullname, m1.spec
ORDER BY fullname, m1.spec

edited Jun 24 '16 at 14:27

answered Jun 24 '16 at 01:09

Barmar

741,623
53
500
612

thank you for your answer. it's not working because `spec` is from med table not viz. – k4br4s Jun 24 '16 at 01:38
Oops, I got confused. Try it now. – Barmar Jun 24 '16 at 01:43
Now it gives error code 1052. column `spec` in field list is ambiguous. I think this could be done with union. I'll keep reading and come back with an answer. – k4br4s Jun 24 '16 at 10:52
I had `v1.spec` it should have been `m1.spec`. – Barmar Jun 24 '16 at 14:27
Still not a good result but I think I got something to work with. Your code returns all records for 3 specs. I need to return top 3 records for each spec. Thank you for your help ! – k4br4s Jun 24 '16 at 20:07
Oh, I completely misunderstood the question. – Barmar Jun 24 '16 at 20:08

select first three values order by a column

1 Answers1