How can I get 2 rows as first reply when databse has more rows with same keywords?

[priscillamuis]

Hi, my database has 4 colums: name, adres phone and fax

I'm running this query:

SELECT DISTINCT '$sender',MSG = substring ((SELECT RTRIM(name)+',' +RTRIM(street)+ ' t:'+RTRIM(telno )+' f:'+RTRIM(faxno )+' - ' FROM autodatabase WHERE
name LIKE '$keyword%' FOR XML path(''), ELEMENTS),0 ,500) FROM
autodatabase

I want the first reply to be the first 2 rows of what has been found.
I get all the rows with the same keyword in the database.

I've run:

SELECT RTRIM(name)+',' +RTRIM(street)+ ' t:'+RTRIM(telno ) AS search
FROM autodatabase
WHERE name LIKE '$keyword%'

But now it doesn't break the rows. It displays it as one long sentence

How can I break the rows but get 2 rows when asking for data en which string do I use to ask for the next rows (row 3 +4) with the same keyword?

Thanks
Priscillamuis

[OraMaster]

Try using TOP keyword.

[priscillamuis]

Do you also think I should use BEGIN LOOP en END LOOP?
I SELECT RTRIM(name)+',' +RTRIM(street)+ ' t:'+RTRIM(telno ) AS search
FROM autodatabase BEGIN LOOP
WHERE name LIKE 'TOPkeyword%'
ORDER BY name ASC

I also don't know if I should use @@ROWCOUNT =2 to use as command to break the data after 2 lines, but to go on with the list I don't know if I should use CONTINUE and stop the script with END LOOP.

WHAT do you think?

[OraMaster]

TOP is something different. It is being used like

select TOP 100 * from <tablename>

[priscillamuis]

Ooh, ok. But I don't know how many rows will be found? Every search will be different. If I don't know the number of rows, what syntax do I use?
SELECT TOP '%' * from autodatabase?

[sashi]

Why not create a simple stored procedure for this purpose?

Count number of rows

  SELECT COUNT(*) FROM tblName WHERE fldName LIKE = 'what_u_want_to_search'

Kindly refer to below attached link,

Learn T-SQL

[priscillamuis]

I've already tried this. It works but it display's the row count. I don't want the row count.
I want to see/ view the information that's in the rows.
How do I do that?

[sashi]

I want to see/ view the information that's in the rows.

What do you mean by information in the row? Can't see the information with a simple [SELECT] statement?

[priscillamuis]

Sorry, let me rephrase: I want to find the rows with the keyword in it, eg 10 rows with red in database. But I want to display 2 rows at a time without losing the whole list e.g red blue and red yellow. The other red green, red magenta, red white etc) The query should divide the list into 2 rows of 160char en let the list just loop on until I'm back on top again.

[ck9663]

You have to handle that on your front-end, not on the back-end.

Happy Coding!!!

~~ CK