-1
SELECT N.National_PosID, N.Title, C.First_Name, C.Mid_Name, C.Last_Name, count(*)  
FROM National_Position N, Candidate C, Vote V
WHERE N.National_PosID = C.National_PosID AND V.Candidate_ID = C.Candidate_ID
Group by N.National_PosID, N.Title, C.First_Name, C.Mid_Name, C.Last_Name;

The output

+------------+------------+----------+-----------+----------+
| Title      | First_Name | Mid_Name | Last_Name | count(*) |
+------------+------------+----------+-----------+----------+
| Agueda     | Sarahann   |          | MCMAHON   |      557 |
| Amesha     | Maurice    |          | GREEN     |     1071 |
| Kiante     | Jesicca    |          | WINTERS   |      482 |
| Kieffer    | Serene     |          | HIGGINS   |     1285 |
| Koray      | Ahley      |          | COLLIER   |     1287 |
| SENATE     | Daisy      | Hao      | MADDOX    |     1316 |
| SENATE     | Radhames   | Jakson   | MURILLO   |     1269 |
| SENATE     | Rayan      | Kaitlynn | FRYE      |     1089 |
| SENATE     | Rio        | Krysti   | HUGHES    |      457 |
| SENATE     | Shalondra  | Nila     | HORTON    |      551 |
+------------+------------+----------+-----------+----------+
10 rows in set (1.33 sec)

The problem is that how do I get the output of the only the max count only. Only outputting the max votes row. This is run on mysql.

  • Which RDBMS are you using? Please add the relevant tag to your question: oracle, sql-server, mysql, ...? – GMB Oct 22 at 1:02
  • And what is your expected result? Just one, record that has the maximum count (here: Daisy Maddox)? – GMB Oct 22 at 1:04
  • 1
    Bad habits to kick : using old-style JOINs - that old-style comma-separated list of tables style was replaced with the proper ANSI JOIN syntax in the ANSI-92 SQL Standard (more than 25 years ago) and its use is discouraged – marc_s Oct 22 at 4:51
0

you can use subquery to get the max count of your original result.

SELECT t1.National_PosID, t1.Title, t1.First_Name, t1.Mid_Name, t1.Last_Name, max(ct)
FROM(
    SELECT N.National_PosID, N.Title, C.First_Name, C.Mid_Name, C.Last_Name, count(*) ct 
    FROM National_Position N
    INNER JOIN Candidate C ON N.National_PosID = C.National_PosID
    INNER JOIN Vote V ON V.Candidate_ID = C.Candidate_ID  
    INNER JOIN      
    GROUP BY N.National_PosID, N.Title, C.First_Name, C.Mid_Name, C.Last_Name) as t1
GROUP BY t1.National_PosID, t1.Title, t1.First_Name, t1.Mid_Name, t1.Last_Name
0

If you are using Oracle (the way you display the results of your query suggests that), then you can use the FETCH FIRST N ROWS ONLY syntax to exhibit only the record that has the maximum count. This syntax is available since Oracle 12.1.

SELECT 
    N.National_PosID, 
    N.Title, 
    C.First_Name, 
    C.Mid_Name, 
    C.Last_Name, count(*) cnt
FROM 
    National_Position N
    INNER JOIN Candidate C ON N.National_PosID = C.National_PosID
    INNER JOIN Vote V ON V.Candidate_ID = C.Candidate_ID
GROUP BY 
    N.National_PosID, 
    N.Title, 
    C.First_Name, 
    C.Mid_Name, 
    C.Last_Name
ORDER BY cnt DESC
FETCH FIRST 1 ROWS ONLY;

In Mysql (and Postgres), just replace FETCH FIRST 1 ROWS ONLY with LIMIT 1.

Note: implicit joins have fallen out of favor long ago. Always use explicit joins. I have modified your query accordingly.

SELECT *
FROM (
    SELECT 
        N.National_PosID, 
        N.Title, 
        C.First_Name, 
        C.Mid_Name, 
        C.Last_Name, count(*) cnt
    FROM 
        National_Position N
        INNER JOIN Candidate C ON N.National_PosID = C.National_PosID
        INNER JOIN Vote V ON V.Candidate_ID = C.Candidate_ID
    GROUP BY 
        N.National_PosID, 
        N.Title, 
        C.First_Name, 
        C.Mid_Name, 
        C.Last_Name
    ORDER BY cnt DESC
) x 
WHERE ROWNUM = 1
  • Yes it does output the rows in descending order but do to the fact that mysql doesnt accept where rownum and fetch first 1 rows only. It does not have this function – Sheng Zhang Oct 22 at 1:40
  • @ShengZhang: In Mysql (and Postgres), just replace FETCH FIRST 1 ROWS ONLY with LIMIT 1. – GMB Oct 22 at 6:22
  • Yes I realized that but the limit 1 is only limiting the first row with the max count, but there is a difference in each position since the national is separated into president and senate position and so need the max count of two position. – Sheng Zhang Oct 22 at 17:29
  • @ShengZhang: ok now I think I see what you want. That would have been easier if you had provided your expected results in the first place, as I initially commented. Can you please tell me which version of mysql are you using? – GMB Oct 22 at 19:12
  • The version is 5.1 – Sheng Zhang Oct 22 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.