CODEWITHSUNDEEP
mysqlsqlgroup-by
Reuben Gomes
Reuben Gomes

Reputation: 878

Group by city name and only display the same city name once in the first row

I have a table

city|locality
a   |  bc
a   |  dc
a   |  ef
a   |  gh
a   |  ij

I want to create a group by so that it shows

a |bc
  |dc
  |ef
  |gh
  |ij

I'm currently using

select(*) from 'tablename' group by city;

but it only gets me one locality value.

Upvotes: 2

Views: 1893

Answers (4)

Mark Rotteveel
Mark Rotteveel

Reputation: 109024

The solution is to just use:

select city, locality from yourtable order by city, locality

You can then solve the problem in your presentation layer by only printing/using city if the value changes.

Something like (in Java):

String previousCity = null;
while (rs.next()) {
    String currentCity = rs.getString("city");
    if (Objects.equals(previousCity, currentCity) {
        // Same as previous: no need to print so set blank
        String currentCity = "";
    } else {
        previousCity = currentCity;
    }

    System.out.println(currentCity + " | " + rs.getString("locality"));
}

Upvotes: 1

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

Try this, 

select col1, GROUP_CONCAT(col2 SEPARATOR ';') from tablename;

It will give you the result like:

a | bc;dc;ef;gh;ij

Now use it in your own way.

Upvotes: 0

Perry
Perry

Reputation: 153

Not pretty sure if this is what you want, but if you want to get all rows from locality for a in one new cell, you can use this.

SELECT *, GROUP_CONCAT(locality) as localities FROM table GROUP BY city

This will output:

city    localities 
a       bc, dc, ef, gh, ij

Upvotes: 1

Jasir alwafaa
Jasir alwafaa

Reputation: 586

You can try by writing 2 queries,

select distinct (city) from 'tablename';

then u will get city as 'a';

select * from tablename where city='a';// 'a' means out put of first query

Upvotes: -1

Related Questions