Complex order by

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
LVL 8
thenoneAsked:
Who is Participating?
 
EDDYKTCommented:
really confuse. may be just do

select * from table 1 order by column1 desc
0
 
g_johnsonCommented:
i don't get it     more explanation please
0
 
thenoneAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
g_johnsonCommented:
what i don't understand is why is "dogs are great" first before "dogs"
and what is the exact layout of your table?
0
 
thenoneAuthor Commented:
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
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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
 
thenoneAuthor Commented:
its a recordset from a mysql database.
0
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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
 
thenoneAuthor Commented:
well actually i want it in my sql statement most likely in my order by is that possible?
0
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
**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
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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
 
thenoneAuthor Commented:
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
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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
 
thenoneAuthor Commented:
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
 
Leo EikelmanDirector, IT and Business DevelopmentCommented:
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.....

what do your tables look like in your database?

Leo
0
 
thenoneAuthor Commented:
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
 
thenoneAuthor Commented:
because dogs show up in column1 first so the appear first by their id.
0
 
thenoneAuthor Commented:
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
 
EDDYKTCommented:
do you got it?

select column1, column2, id from table1 order by column1 desc
0
 
thenoneAuthor Commented:
would that also order by id as well?The actual number?
0
 
EDDYKTCommented:
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
 
thenoneAuthor Commented:
what is asc? Assending?
0
 
EDDYKTCommented:
sort by ascending or descending order
0
 
thenoneAuthor Commented:
ok I will give that a try thank you!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.