Android Question SQL syntax: how I select only "representative" records from Database?

[bsnqt]

Hi All,

My question is related to SQL syntax, please help. Thank you very much.

Let say I have some records in the database like:

_id          name         phone_number         message         card_number
==============================================================================
1         John              111111111         John is a teacher     123456678
2         Derrick           222222222         Derrick is a man      123456699
3         Smith             333333333         Smith is at home      123457799
1         John              111111111         John has left         123456678
2         Derrick           222222222         Derrick is drinking   123456699
1         John              111111111         Where is John?        123456678
7         Steve             444444444         Steve is here         123452211
4         Mary              555555555          Mary is singer       321135672
3         Smith             333333333         Smith is fine         123457799

Some of name repeats few times (John = 3 times, Derrick = 2 times, Smith = 2 times). I want to select only their "representative" rows (records), i.e. I want to have all names but each can appear one time only (maybe their newest record). What I want to achieve is something like this:

_id          name         phone_number         message         card_number
==============================================================================
1         John              111111111         John is a teacher     123456678
2         Derrick           222222222         Derrick is a man      123456699
3         Smith             333333333         Smith is at home      123457799
7         Steve             444444444         Steve is here         123452211
4         Mary              555555555          Mary is singer       321135672

What is the syntax/condition I should query with SELECT?

 

[bsnqt]

Ok, I found it out, but not sure it is correct or not:

SQL.ExecQuery("SELECT DISTINCT _id FROM table1")

Now another issue I cannot know how to resolve is that I need a total quantity of rows per each name that I already filtered... For example (from the example above) how I know that John has 3 rows in the database.

 

[Merlot2309]

Hello,

You can use theCursorName.RowCount

 

[wl]

Have a look at the GROUP BY clause

Select name, count(_id) from table group by name

 

[wl]

@Merlot2309:

Hello,
You can use theCursorName.RowCount

I don't think you can use this as it will return the number of rows for the cursor: so the total amount of record returned and not the number of rows per "name" unless you would run a second query for each "name"

 

[bsnqt]

Hello,

You can use theCursorName.RowCount

Thank you, do you mean I need to go by each name and query again (with condition "WHERE name = John")?

I would agree with wl. It will take again one more query...

 

[bsnqt]

Have a look at the GROUP BY clause

Select name, count(_id) from table group by name

Thanks wl, let me try it out...

Edit: It works, thanks a lot wl

 

[Merlot2309]

Since it's always nice to learn from others: thank you WL

I use SELECT distinct ...... group by ......
For i = 0 to cursor.rowcount -1

to subtract an alphabet from a database (11.000 records) and it's working fine with rowcount.