Android Question Query sql with group by

[FabioCirillo]

Hi,
i'm new member, i've the problem with query sqlite.
I've this record load into spinner:

player 1
player 2
player 3
player 1
player 4

etc

i would not to see double name but i'd see only different names for example:

player 1
player 2
player 3
player 4

so I thought of using the group by sql but not working. here is my code

sqldb.ExecQuery("SELECT id,name FROM player GROUP BY name")

where am I wrong? can you give me some help?

 

[sorex]

SELECT distinct id,name FROM player order by name

 

[FabioCirillo]

does not work always shows me duplicate names

 

[sorex]

bizar, are you sure the updated app is running and not some old compile?

 

[FabioCirillo]

I am sorry it is my mistake, I forgot to write that there is also the column id (key) then the records you see are as follows:

1 player1
2 player2
3 player3
4 player1
5 player4

and I've compiled a new release

 

[sorex]

ok, you are not supposed to have twice player1 in your recordset it indicates that they are 2 different persons.

wich ID will you pick for player1 then? your other results won't be right unless you keep all this in mind.

 

[FabioCirillo]

I need to display different names but also have the id behind I'll show you the entire code:

splocation.Clear
Dim cursors As Cursor
cursors=sqldb.ExecQuery("SELECT distinct id,location FROM location ORDER BY location")
For i = 0 To cursors.RowCount - 1
cursors.Position = i
splocation.Add(cursore.GetString("location"))
spinnermap.Put(cursore.GetString("location"),cursore.GetInt("id"))
Next
cursors.Close
'--------------------------------

 

[sorex]

the code is right but your data isn't.

see my previous post.

 

[Reviewnow]

Your id column is an autonumber field , therefore there is no way to group on it

your correct query would be
select name from player group by name order by name

 

[FabioCirillo]

ok but i need get id too

 

[klaus]

Do you have other columns in the player table ?

 

[Straker]

ok but i need get id too

If you have
Id Name
1 player1
2 player5
3 player1

With 'select distinct name, id from...' You may obtain distinct names BUT for player1 you will of course have only one of the two Id.

 

[sorex]

no, it will only show one for identical records (based on the selected name & id) and since the IDs are not equal it still shows 2.

he should fix his second player1 name to player5 or something.

 

[Straker]

no, it will only show one for identical records (based on the selected name & id) and since the IDs are not equal it still shows 2.

he should fix his second player1 name to player5 or something.

Ok, maybe it's so with SQLite. With other SQL db the distinct works only on the first field and you need parenthesis if you want a multifield distinct. I'm not using SQLite, sorry for the wrong answer.

 

[sorex]

as far as I recall from access, mssql and mysql distinct is a keyword that counts for everything of the select that comes after it.
You can verify it by swapping the fields in the select, the result should be the same.

here it looks if the combination of id & name is unique, which isn't due to the id in the distinct select and the double playername.

 

[imbault]

SELECT distinct name FROM player GROUP BY name
is correct

 

[sorex]

right (but distinct is actually the same as group by so the group by can be dropped in this case), if he didn't need the id. that's the point of all these replies

his data is not comform to the output he wants.