Mysql group table with two column

Question

I need to group my table, my table had lot of columns but i am showing only two column jtcno and projectno. The primary key is roeid, it contain an auto increment numbers. My table structure is as given below

+-------+-----------+
| jtcno | projectno |
+-------+-----------+
| 8229  |     10636 |
| 8230  |     10636 |
| 8231  |     10636 |
| 8229  |     10637 |
| 8230  |     10637 |
| 8231  |     10637 |
| 8229  |     10637 |
| 8230  |     10637 |
| 8231  |     10637 |
+-------+-----------+

I need to group the table according to jtcno as follows

+-------+-----------+
| jtcno | projectno |
+-------+-----------+
| 8229  |     10636 |
| 8230  |     10636 |
| 8231  |     10636 |
| 8229  |     10637 |
| 8230  |     10637 |
| 8231  |     10637 |
+-------+-----------+

Can anyone help me to write the query. Thank you in advance.

What is your PRIMARY KEY? And see [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Sep 27 '20 at 09:29

score 1 · Answer 1 · answered Sep 27 '20 at 09:35

1

This is the query you seem to want here:

SELECT DISTINCT jtcno, projectno
FROM yourTable
ORDER BY projectno, jtcno;

answered Sep 27 '20 at 09:35

Tim Biegeleisen

502,043
27
286
360

score 1 · Answer 2 · answered Sep 30 '20 at 07:33

1

Try this query:

select distinct jtcno, projectno from tablename order by jtcno

answered Sep 30 '20 at 07:33

Purvi Barot

241
2
9

aRvi · Accepted Answer · 2020-09-27T09:46:48.287

0

This will give you the desired result, you need to group by both the columns

If there no index

SELECT DISTINCT jtcno, projectno
FROM my_table
ORDER BY jtcno, projectno;

If there an index

SELECT jtcno, projectno
FROM my_table
GROUP BY jtcno, projectno;

edited Sep 27 '20 at 09:46

answered Sep 27 '20 at 09:31

aRvi

2,203
1
14
30

1

There is no problem in sql for which the above is a valid solution. In the absence of any aggregating functions, a GROUP BY clause is never appropriate. If you want DISTINCT results, use DISTINCT. – Strawberry Sep 27 '20 at 09:35
@Strawberry I have corrected my answer, I was not knowing this – aRvi Sep 27 '20 at 09:41
@Strawberry AS far as I have read `DISTINCT` is preferable if there is no index and `GROUP BY` is preferable if there is an index. https://stackoverflow.com/questions/581521/whats-faster-select-distinct-or-group-by-in-mysql – aRvi Sep 27 '20 at 09:45
Copy of my answer. Your original query was incorrect. – Tim Biegeleisen Sep 27 '20 at 12:38

Mysql group table with two column

3 Answers3