Android Question Sorting SQL Items based on data in the column

[aaronk]

Hello,

I am trying sort my database alphabetically based on the data stored in the columns, but not sure how to do it.

Here is how I have created my database (well it's a example on how my database is setup):

Sub Process_Globals
	Dim SQL_Database As SQL
End Sub

Sub Globals
	Dim item_status(257) As String ' This is set on Activity_Create    0=green   |   1=red   |   2=yellow
End Sub

Sub Activity_Create(FirstTime As Boolean)
	File.Delete(File.DirDefaultExternal, "MyDatabase.db") ' Delete the MyDatabase.db while testing (so it always has a default database while testing)

	' create database and load table
	SQL_Database.Initialize(File.DirDefaultExternal, "MyDatabase.db", True)
	Dim data As String
	data = "CREATE TABLE items (id INTEGER PRIMARY KEY"
		For k = 1 To 256
	    	data = data & ", header" & k & " TEXT"
		Next
		data = data & ")"
	SQL_Database.execNonQuery(data) ' create the table	
	data = ""
	
	' Load dummy names into the database for testing
	data = "INSERT INTO items VALUES (NULL"
		For k1 = 1 To 5
    		data = data & ",'" & "item " & k1 & "'"
			item_status(k1) = "0" ' 0=green   |   1=red   |   2=yellow
		Next
		For k1 = 6 To 20 ' no names for items 6-20
    		data = data & ",'" & "" & "'"
			item_status(k1) = "0" ' 0=green   |   1=red   |   2=yellow
		Next
		For k1 = 21 To 256
    		data = data & ",'" & "item " & k1 & "'"
			item_status(k1) = "1" ' 0=green   |   1=red   |   2=yellow
		Next
		data = data & ")"
		SQL_Database.execNonQuery(data) ' load the default data into this table
		data = ""
End Sub

As you will see I have a heap of columns (header1, header2.. header256)
The item_status is a string that will store the status of that item (something I am going to do with it later on)

I need to only select 1 row and log each of the 256 headers alphabetically.
However, I also need to map the item_status with the header that we sorted alphabetically (so that they match up)

The only thing I was going to use was using the code from post 9 from this link: http://www.b4x.com/android/forum/th...hms-teaching-with-basic4android.8548/#content

However, based on the code from post 9 from that link I am not sure how to do it (or if it's even the best way to do it)

Does anyone have any ideas on how I can log the items in my database alphabetically as I can't seem to work it out ?
I do plan to work with the values it logs later on in my app, so by storing them in the app to use later would be a bonus. (one of the reasons I looked at the post from that link above)

 

[eps]

All you need to do is order the data when you retrieve it.

I think we'd need a little more information on the table you are using.

But basically a select statement with order on specific columns, either ASC or DESC depending on if you need it sorted ascending or descending.

Ordering of data does incur some overheads, but if you put an index in place this can quite often speed things up so that it doesn't matter too much.

something like :

select * from table order by name1, col2, col3, col4

or

select * from table order by name1, col2, col3, col4 desc

and so on. Take a look at... http://www.sqlite.org/lang.html for some light reading.

 

[DonManfred]

Can you please describe more about the facts?

Which tables you have?
Which fields the tables have?
Some example-data?
What is your goal? example lyout, output, whatever
What´s the code to read the table, data, whatever?

If i understand correctly you are serching for a way do order the entries from an sql query. If so. SQL can order it for you.

In Mysql you could do something like

select fielda, fieldb from tablec where fieldd=1 order by fielda asc, fieldb desc

This would order the results first by fielda Ascending and then by fieldb descending
SQLite must have it´s own syntax to order a resultset. But i think that should be work in sqlite like in mysql ;-)

 

[aaronk]

Hello,

Thanks for your reply's.

Lets see if I can re-word what I am trying to do.
All the code I have used is in Post 1. When you load the app it will create the database and load some dummy data in the table for testing.

Database is called: MyDatabase.db
Table is called: items
Table (items) looks like this:

What I am trying to do is alphabetically sort the values stored in each of the columns (header1, header2, header3... right though to header256).

For this example, lets say I only have 4 columns rather then the 256.

row id 1 has the following:
header1 = Bob
header2 = Smith
header3 = <blank, not used>
header4 = Jack
header5 - header256 are left blank.

row id 2 has the following:
header1 = Bill
header2 = John
header3 = Tom
header4 = Aaron
header5 - header256 are left blank.

I need a way to only sort row id 1 only, based on the values stored in each of the columns only for row 1 (not including row 2)
So it would Log:
Bob
Jack
Smith

select fielda, fieldb from tablec where fieldd=1 order by fielda asc, fieldb desc

This would order the results first by fielda Ascending and then by fieldb descending
SQLite must have it´s own syntax to order a resultset. But i think that should be work in sqlite like in mysql ;-)

I don't think that code is going to work as it will be sorting each of the rows and I only want to sort it based on the columns for that 1 row only, not all rows.

 

[elitevenkat]

Create a tmp table with one field = headervalue - string

loop thru the fields (from your data table for a particular row id ) and insert (ignore blank values) the header values into the tmp table. retrieve the tmp table data using sql order by clause!

 

[DonManfred]

Thanx for the next explanation of your problem. Now it´s much clearer.

After seeing and understanding your problem my only thought is:

Your database-design needs a restructure...

You need two tables:

items
- id (unique, autoincrement)
- name (if you want)

INSERT INTO items SET id=1, name = "Event X"

itemdetails
- detailid (unique, autoincrement)
- itemid
- value

Your item itself have one "short" database-entry in items
The names you store in a second table itemdetails

For your example

header1 = Bob
header2 = Smith
header3 = <blank, not used>
header4 = Jack

INSERT INTO itemdetails SET itemid=1, value="Bob"
INSERT INTO itemdetails SET itemid=1, value="Smith"
INSERT INTO itemdetails SET itemid=1, value="Jack"
[..] You can put more than 256 names here with this structure. And even if you just need 4 you dont need one item with 257 fields and 252 emty of them (but they need space in DB even if empty)

To get a names list from one item you need something like

select * from itemdetails
left join items on itemid=id
where id=1
order by Value ASC

With such a sql-query you´ll get (in case if itemid=1) three results

I´m not familar with sqlite but this should work in sqlite in a way like this .

 

[aaronk]

I think I am going to keep the database how I have it set up.

I think having it in different rows will make the database look messy.

 

[LucaMs]

you just have to get a data log?

If so, I do not understand the difficulty!

Read a record using Select * where id = x
and load a list.sort (True) with the result of the query!

 

[aaronk]

you just have to get a data log?

If so, I do not understand the difficulty!

Read a record using Select * where id = x
and load a list.sort (True) with the result of the query!

This is what I am going to do.

I didn't know if there was another/better way to do it, but looks like loading it into a list and sorting the list is going to be the best way.

 

[LucaMs]

the list is sorted automatically, using MyList.Sort(True).

To get only the log, so it is very simple, but I guess you have a purpose more complex and that you will probably have to implement something different.

 

[eps]

Pull the data from the database into an array then order this, repeat for every row. But it's going to be slow, imo

 

[Mahares]

You need a UNION query. It is a little more complicated, but quick and this will yield the correct result if you want to stay with queries:

Dim DBTableName As String = "items"
Dim txt as string
Cursor1=SQL1.ExecQuery("SELECT * FROM " & DBTableName & " LIMIT 1")
Cursor1.Position=0
txt="SELECT " & Cursor1.GetColumnName(0) & " AS Header FROM (SELECT * FROM " & DBTableName & " LIMIT 1) "
For i= 1 To Cursor1.ColumnCount-1
   txt=txt & " UNION ALL SELECT " & Cursor1.GetColumnName(i) & " AS Header FROM (SELECT * FROM " & DBTableName & " LIMIT 1) "
Next
txt=txt & " ORDER BY Header"
'Log(txt)
Cursor1=SQL1.ExecQuery(txt)
For i =0 To Cursor1.RowCount-1
    Cursor1.position = i
    Log(Cursor1.GetString("Header"))  'displays the record fields data sorted
Next

You can replace the LIMIT 1 with a WHERE ID= 1 in your case if you want.