Database

2-Aug-11 1:33

I`m building a web chat service and I use this query:

SELECT * FROM messages WHERE chat_room_id='$chat_room_id'

But I need to display no more than 20 rows (last 20 rows). Can`t use LIMIT because it will return the first 20 rows.
Is there a sql command? or do I have to sort it out in php?

Luc Pattyn2-Aug-11 2:02

Luc Pattyn

2-Aug-11 2:02

the concept of first row/last row does not exist, unless you have an ORDER BY clause, without that the rows are unordered i.e. they come in an arbitrary order.

So whatever criterion it is you want to define the "last rows", write the inverse and use LIMIT(n) or TOP(n) to get the first N rows of that. Use TOP in T-SQL, LIMIT in MySQL.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

samson radu2-Aug-11 2:20

2-Aug-11 2:20

what if I create a temp table with all the selected rows and then return it`s last rows? Smile | :)

Is that a good practice?

Luc Pattyn2-Aug-11 2:28

Luc Pattyn

2-Aug-11 2:28

No. Look at a table as a pile of rows, not an ordered set of rows.

Smile | :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

samson radu2-Aug-11 2:44

2-Aug-11 2:44

I found the best way..since I`ll have to clean up the tables once in a while...if the select returns too many rows I start deleting from the top and select again.

Order by

David Mujica2-Aug-11 2:44

David Mujica

2-Aug-11 2:44

select top 20 * from messages where chat_room_id=$chat_room_id ORDER BY yourTimeStampField DESC

This makes the HUGE assumption that you have a timestamp field defined in your messages table.

Give it a shot. Thumbs Up | :thumbsup:

Crap. I think I just replied to the wrong thread. This reply was meant for the original poster.
Sorry for any confusion. It's early for me.

Re: Order by [modified]

samson radu2-Aug-11 2:52

2-Aug-11 2:52

I have a timestamp but I don t get your idea:
I have:

4:35 HELLO!
4:38 WASSUP?
5:10 FINE!
7:30 OLA!
8:00 BONSOIR!
supposing I`ll output 3 messages max.
why select top 3 messages here and sort desc? it`ll be:

5:10 FINE!
4:38 WASSUP?
4:35 HELLO!

when I want:

5:10 FINE!
7:30 OLA!
8:00 BONSOIR!

this works great and keeps the table clean:

$result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'");
$chat_num=$result->num_rows();
if ($chat_num>30)
{ $to_delete=$chat_num-30;
$query=$this->db->query("DELETE FROM messages WHERE chat_room_id='$chat_room_id' LIMIT $to_delete");
$result=$this->db->query("SELECT * FROM messages WHERE chat_room_id='$chat_room_id'");
}
return $result;

modified on Tuesday, August 2, 2011 8:58 AM

Re: Order by

PIEBALDconsult2-Aug-11 2:57

PIEBALDconsult

2-Aug-11 2:57

Try it.

Re: Order by

samson radu2-Aug-11 3:06

2-Aug-11 3:06

already did.

Error trapping in SQL Server 2005 [modified]

Nagy Vilmos2-Aug-11 22:27

Nagy Vilmos

2-Aug-11 22:27

Further to Luc's excellent advice, I would add that you should avoid the death trap SELECT * FROM ... syntax. It's quick now but will come back and bite your head off in the future. Name the columns you want explicitly and query nothing else.

Panic, Chaos, Destruction. My work here is done.
Drink. Get drunk. Fall over - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer
Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett

vanikanc1-Aug-11 7:37

1-Aug-11 7:37

Hello,

I have not really coded for error trapping the past since the applications were not that big. Currently, the app being worked on is huge with multiple phases development. I wanted to find out the best practices for error trapping, some simple forms and some more complex ones. I need to start using them

It is also ok, to write them to the database tables if possible.

I know I can google this as well but some times it takes a longer amount of time to find the correct link/article.

Thank you for your time!

modified on Monday, August 1, 2011 3:43 PM

jschell1-Aug-11 8:25

jschell

1-Aug-11 8:25

There is no way to trap everything.

And it is non-sensical as well.

So you need to determine some actual implementation cases where it is reasonable.

vanikanc wrote:
It is also ok, to write them to the database tables if possible.

Still depends on what you mean by error.
It is certainly possible for some cases.

Also why is this just a database relevant question? If the system is large then there will be other parts of the system which will fail in relation to the database. For example bouncing the database is very likely to cause ripple failures in a 24x7 system unless those other systems are specifically designed to deal with that.

vanikanc1-Aug-11 8:29

1-Aug-11 8:29

Even if there are some scenarios i would like to trap for, which syntax would I use?

Like C# has a Try, Catch, Finally block I use for displaying som error messgaes.

jschell2-Aug-11 9:18

jschell

2-Aug-11 9:18

SQL server has try/catch.

But you need to be careful with it.

One might think that they could put that in every proc as thus record errors but I am rather certain that in many procs the errors that might occur would be the ones that would be compounded by attempting another write to the database. For example a table space error.

Shameel1-Aug-11 8:59

Shameel

1-Aug-11 8:59

SQL

BEGIN TRY
-- Write code that can throw error
END TRY
BEGIN CATCH
-- Catch error
END CATCH

Mycroft Holmes1-Aug-11 13:01

Mycroft Holmes

1-Aug-11 13:01

All the data validation is done in the business layer so the data hitting the database is clean and I write perfect stored procedures so I don't need error trapping in the database.

Once a procedure is coded it generally does not break unless there is a change in structure. The few cases where I can identify this may be prevalent is where I use try/catch structures.

I do have a trigger based logging system but that is not error trapping.

Never underestimate the power of human stupidity
RAH

thatraja1-Aug-11 15:00

thatraja

1-Aug-11 15:00

vanikanc wrote:
I know I can google this as well but some times it takes a longer amount of time to find the correct link/article.

Then give a shot in CP.
Overview of Error Handling in SQL Server 2005[^]
A Closer Look Inside RAISERROR - SQLServer 2005 [^]

thatraja

My Tip/Tricks
My Dad had a Heart Attack on this day so don't...

Dynamic sort

vanikanc1-Aug-11 2:54

1-Aug-11 2:54

Hello,

I want to pass the sort as an input parameter to the stored proc.

I am constructing a dynamic sql and executing it. The problem is more like a syntax issue, where in I an checking for certain value in sql.
----
Set @sql1 = '
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status='N'
ORDER by'

set @sql1 = @sql1 + ' ' + @sort
---------

How would I check for additional conditions in this sql? I tried to enclose N in double still did not work.

Many thanks!

Herman<T>.Instance1-Aug-11 3:15

Herman<T>.Instance

1-Aug-11 3:15

you should use '''N'''

In Word you can only store 2 bytes. That is why I use Writer.

vanikanc1-Aug-11 3:45

1-Aug-11 3:45

Thank you for your response.

It should be within two single quotes, like
''N''

Re: Dynamic sort [modified]

Corporal Agarn1-Aug-11 5:18

Corporal Agarn

1-Aug-11 5:18

That would be ''''N''''
try:

declare @sql1 varchar(2000)
Set @sql1 = '
    SELECT distinct
    sd.storeno storeno,
    sd.storename storename
    FROM
    test_table sd
    where sd.status=''''N''''
    ORDER by'
 
print @sql1

Output:
SELECT distinct
sd.storeno storeno,
sd.storename storename
FROM
test_table sd
where sd.status=''N''
ORDER by

Shameel1-Aug-11 5:30

Shameel

1-Aug-11 5:30

When you wish to embed single quotes within dynamic strings, use two consecutive single quotes, like this:

SQL

Set @sql1 = ' 
SELECT distinct 
sd.storeno storeno, 
sd.storename storename
FROM 
test_table sd
where sd.status=''N''
ORDER by'
 
set @sql1 = @sql1 + ' ' + @sort

EDIT: Corrected a mistake.

modified on Monday, August 1, 2011 2:49 PM

vanikanc1-Aug-11 7:34

1-Aug-11 7:34

I don't know but just the double single quotes worked for me.

''N''