MySQL Select Query

Hello could anyone please help me make a MySQL query for this stuff

i have a table with a field grp which contains a string like '0|1|2|3|' and another field pos which contains a similar string but with each number indicating the position of the item in that particular group.

i.e.

LET:
grp = '1|2|3|4'
pos = '5|3|2|8'

WHERE:
pos at '5|3|2 ...' indicates the position of the record for the particular group grp at '1|2|3...'


now i want to make a search where it returns only rows containing '0|' from the grp field but sorting them relative to the pos value.

some of you might think the database tables need to be redesigned ... but sometimes old people (my boss) are just difficult to convince and they want what they want. the thing now is that im stuck with that SQL query problem.

I'd gladly appreciate any help regarding this matter .. thanks

 

I am not sure if i understand correctly, but is this what you need ?

Select * from grp inner join pos on grpid.pos = pos.grpid where grp =0 order by pos

Everything is depending on how in sql table grp and post are related (with what column)

The other option is to make a question like this

Select * from grp,pos where grp=o order by pos;

Hope this helps

 

not likely

grp and pos are fields in one table


let's say that grp = '0|1|2|3|' and pos = '2|3|1|0'

so the query is something like:

SELECT pos,grp FROM tblRecords WHERE grp LIKE '%0|' ORDER By < this order clause is my problem >

then the result will have to be sorted according to the value of field pos but extracting only the first number which happens to be '2' as the sortorder

but if the select statement is something like

SELECT pos,grp FROM tblRecords WHERE grp LIKE '%1|' ORDER By < this order clause is my problem >

then the result will have to be sorted according to the value of field pos but extracting only the second number which happens to be '3' as the sortorder

hope that makes the problem clearer

 

Something like this might work.

ALTER TABLE tblRecords ADD FULLTEXT(pos)

SELECT pos,grp,MATCH(pos) AGAINST('0|') as myorder FROM tblRecords WHERE grp LIKE '%0|' ORDER By myorder

 

haha thanks for the info guyz .. it did help