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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

g_johnsonCommented:
i don't get it     more explanation please
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
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?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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')
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
thenoneAuthor Commented:
its a recordset from a mysql database.
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


thenoneAuthor Commented:
well actually i want it in my sql statement most likely in my order by is that possible?
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
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
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.
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
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")
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
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")

thenoneAuthor Commented:
because dogs show up in column1 first so the appear first by their id.
EDDYKTCommented:
really confuse. may be just do

select * from table 1 order by column1 desc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
EDDYKTCommented:
do you got it?

select column1, column2, id from table1 order by column1 desc
thenoneAuthor Commented:
would that also order by id as well?The actual number?
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
thenoneAuthor Commented:
what is asc? Assending?
EDDYKTCommented:
sort by ascending or descending order
thenoneAuthor Commented:
ok I will give that a try thank you!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.