Member Avatar for ROTC89

ok so i am doin a table that has info on all 44 presidents. i have to show the state the produces the biggest number of presidents...i am not sure how to do it. here is a lil bit of the table for you . I want it to show that VA has the most presidents from it. help please

| id | name | middle | last | bdate | ddate | yr_sworn | yr_left | party | home_town | state | vp |
+----+------------+----------------+------------+----------+----------+----------+---------+---------------------+----------------+-------+-------------------------------------+
| 1 | George | n/a | Washington | 17320222 | 17991214 | 1789 | 1796 | Federalist | Westmoreland | Va | John Adams |
| 2 | John | n/a | Adams | 17351030 | 18260702 | 1797 | 1801 | Federalist | Quincy | Ma | Thomas Jefferson |
| 5 | James | n/a | Monroe | 17580428 | 18310704 | 1817 | 1825 | Democrat-Republican | Westmoreland | Va | Daniel D Tompkins |
| 3 | Thomas | n/a | Jefferson | 17430413 | 18260804 | 1801 | 1809 | Democrat-Republican | Shadwell | Va | Aaron Burr, George Clinton |
| 4 | James | n/a | Madison | 17510316 | 18360728 | 1809 | 1817 | Democrat-Republican | Port Conway | Va | George Clinton,Elbrige Gerry |
| 6 | John | Quincy | Adams | 17670711 | 18480223 | 1825 | 1829 | Democrat-Republican | Quincy | Ma | John C Calhoun |

  • 2 Contributors
  • 2 Replies
  • 109 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by davidn

Dani AI

Generated

As pointed out, the right idea is to group by state and count rows. For a quick answer in MySQL the simplest, readable pattern is to aggregate and sort, then take the top row. Use COUNT(*) (safer than counting a specific column) and normalize the state text to avoid differences like Va vs VA:

SELECT UPPER(TRIM(state)) AS state, COUNT(*) AS presidents
FROM presidents
WHERE state IS NOT NULL AND TRIM(state) <> ''
GROUP BY UPPER(TRIM(state))
ORDER BY presidents DESC
LIMIT 1;

If you need to return all states that tie for the top count (not just one), use a query that compares each group's count to the overall maximum. In MySQL 8+ you can use window functions; in older MySQL versions use a grouped subquery in the HAVING clause. Example for portability:

SELECT UPPER(TRIM(state)) AS state, COUNT(*) AS presidents
FROM presidents
GROUP BY UPPER(TRIM(state))
HAVING COUNT(*) = (
  SELECT MAX(c) FROM (SELECT COUNT(*) AS c FROM presidents GROUP BY UPPER(TRIM(state))) AS t
);

About inserting "a row with a different number between two rows": relational tables have no inherent display order. If you want stable ordering, add an explicit display_order integer column, initialize it, then shift values to make room and insert the new row with the desired position. Do not renumber or rely on the auto-increment id for display purposes — changing primary keys can break foreign keys and queries. When showing results, always use ORDER BY display_order (or another deterministic column) so the presentation matches the intended sequence.

Recommended Answers

All 2 Replies

Member Avatar for ROTC89

also how would i be able to insert a row with a different number between two rows?

Member Avatar for davidn

You should be able to do a group by on the state and then select the max of the counts of names.

It'd be something like:

select max(totalnames) as highest_total from ( select state, count(names) as totalnames from t3 group by state) as temptable;
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.