Android Question Query sql with group by

F

FabioCirillo

Member
Licensed User
Longtime User
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?
 
Sort by date Sort by votes
S

sorex

Expert
Licensed User
Longtime User
bizar, are you sure the updated app is running and not some old compile?
 
Upvote 0
F

FabioCirillo

Member
Licensed User
Longtime User
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
 
Upvote 0
S

sorex

Expert
Licensed User
Longtime User
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.
 
Upvote 0
F

FabioCirillo

Member
Licensed User
Longtime User
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
'--------------------------------
 
Upvote 0

Reviewnow

Active Member
Licensed User
Longtime User
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
 
Upvote 0

Straker

Active Member
Licensed User
Longtime User
FabioCirillo said:
ok but i need get id too
Click to expand...

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.
 
Upvote 0
S

sorex

Expert
Licensed User
Longtime User
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.
 
Upvote 0

Straker

Active Member
Licensed User
Longtime User
sorex said:
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.
Click to expand...

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.
 
Upvote 0
S

sorex

Expert
Licensed User
Longtime User
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.
 
Upvote 0
S

sorex

Expert
Licensed User
Longtime User
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.
 
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…