Solved

# Complex order by

Posted on 2006-04-19
209 Views
if I am full text searching against a string with two columns how can I make it so column a records show up first and ordered by their max id.

example

dogs id = 5 <<this would be second
you have to love dogs

dogs are great id =6 <<<this would be first
the cats meow

cats among humans  <<<This would be third
dogs are great id = 10
0
Question by:thenone

LVL 4

Assisted Solution

i don't get it     more explanation please
0

LVL 8

Author Comment

I want it to basically column1 will show up first according by the id

dogs id ("column1")= 5 <<this would be second
you have to love dogs("column2")

dogs are great id =6 <<<this would be first
the cats meow

cats among humans  <<<This would be third
dogs are great id = 10
0

LVL 4

Expert Comment

what i don't understand is why is "dogs are great" first before "dogs"
and what is the exact layout of your table?
0

LVL 8

Author Comment

dogs id ("column1")= 5 <<this would be second
you have to love dogs("column2")

dogs are great id =6 <<<this would be first
the cats meow

cats among humans  <<<This would be third
dogs are great id = 10

(<<<< dogs are great would be first becasue of the id and dogs is in column1

basically if I am doing select column1,column2 from table where match(column1,column2) against('dogs')
0

LVL 8

Assisted Solution

Where is this information coming from?

>>dogs id = 5
>>you have to love dogs

etc...

Is it stored in a database and you are using a record set?  Or is this in a text file somewhere?

Leo
0

LVL 8

Author Comment

its a recordset from a mysql database.
0

LVL 8

Expert Comment

then all u have to do is

numberOfFields = rs.recordcount
for x = 0 to numberOfFields
rs.movefirst

for y = 0 to numberOfFields

next
next

0

LVL 8

Author Comment

well actually i want it in my sql statement most likely in my order by is that possible?
0

LVL 8

Expert Comment

**OOPS I posted before completing it**

then all u have to do is

location = 0
largest = 0

numberOfFields = rs.recordcount
for x = 0 to numberOfFields
rs.movefirst
for y = x to rs.recordcount
strIdField = CStr(rs.fields("nameOfIdField").value)
id = CInt(Right(strIdField, 1))
if (id > largest)
location = y
largest = id
end if
rs.movenext
next
HERE YOU PRINT OUT rs(location).fields("nameOfIdfield").value and rs(locatoin).fields("theSecondfield").value
rs(location).delete
location = 0
largest = 0
next

I am not sure how you are printing or where you are printing these fields but this is the logic.

If you post your code I might be able to edit the code

Leo
0

LVL 8

Expert Comment

If you wanted to do it in your SQL statement you would have to create a seperate column called ID and order it by that.  I don't think you can do "substring" in SQL calls.

Leo
0

LVL 8

Author Comment

I do have a seperate column called id.

for example select distinct column1,column2 from table1 where match(column1,column2) against('dogs') order by id.

Ive tried this and it just orders the fields as they are completley matched by the id.I need something like

select distinct column1,column2 from table1 where match(column1,column2) against('dogs') order by if column1 = ('dogs') >>>>0,0,1> or soemthing of that nature so that the fields will be manipulated so the the fields with column1 presents will show up first according to the id as I gave in the example.
0

LVL 8

Expert Comment

I think I'm confused in what you are actually trying to do.

I thought you wanted the columns printed out order by ID.

so if you had an ID column all you would have to do is

Select Id, someColumn, someOtherColum from table1 order by Id

Can you explain again how you want them ordered?

Leo
0

LVL 8

Author Comment

dogs id ("column1")= 5 <<this would be second
you have to love dogs("column2")

dogs are great id =6 ("column1")<<<this would be first
the cats meow("column2")

cats among humans  <<<This would be third
dogs are great id = 10("column2")
0

LVL 8

Expert Comment

I'm still confused....  Why are the id's in column1 in the first two

>>dogs id ("column1")= 5 <<this would be second
>>you have to love dogs("column2")

>>dogs are great id =6 ("column1")<<<this would be first
>>the cats meow("column2")

and then the ID is in column2 for the last one

>>cats among humans  <<<This would be third
>>dogs are great id = 10("column2")

Plus, the order doesn't make sense to me.  By Id's, the order is 6, 5, 10.....

Leo
0

LVL 8

Author Comment

Im sorry column1 has dogs,dogs are great,cats among humnas but seperatley.

column2 has you have to love dogs,the cats meow,dogs are great but seperatly

id              column1                              column2
5                dogs                                    you have to love dogs
6               dogs are great                      the cats meow
10             cats among humans              dogs are great

would display like this

dogs id ("column1")= 5 <<this would be second
>>you have to love dogs("column2")

>>dogs are great id =6 ("column1")<<<this would be first
>>the cats meow("column2")

and then the ID is in column2 for the last one

>>cats among humans  <<<This would be third
>>dogs are great id = 10("column2")

0

LVL 8

Author Comment

because dogs show up in column1 first so the appear first by their id.
0

LVL 26

Accepted Solution

really confuse. may be just do

select * from table 1 order by column1 desc
0

LVL 8

Author Comment

I can't do just order by column1 what about if column = ('dogs') <<<<0,0,1,max(id)

wouldn't something like that would return column ones first that match dog and order it by their maximum id.
0

LVL 26

Expert Comment

do you got it?

select column1, column2, id from table1 order by column1 desc
0

LVL 8

Author Comment

would that also order by id as well?The actual number?
0

LVL 26

Expert Comment

no if you specific it i.e.

select column1, column2, id from table1 order by column1 desc, id asc

this will sort column1 first and then id
0

LVL 8

Author Comment

what is asc? Assending?
0

LVL 26

Expert Comment

sort by ascending or descending order
0

LVL 8

Author Comment

ok I will give that a try thank you!!!
0

## Featured Post

Iâ€™ve seen a number of people looking for examples of how to access web services from VB6.  Iâ€™ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web servâ€¦
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture. Â  There is also a companion Debug Toolbar that looks like the followinâ€¦
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can launâ€¦
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦