• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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
0
thenone
Asked:
thenone
  • 12
  • 6
  • 4
  • +1
3 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 EikelmanCommented:
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 EikelmanCommented:
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 EikelmanCommented:
**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 EikelmanCommented:
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 EikelmanCommented:
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 EikelmanCommented:
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
 
EDDYKTCommented:
really confuse. may be just do

select * from table 1 order by column1 desc
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 12
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now