previous row

Hi,
I am trying to do a query where I want to show not only the relevant results
but the previous line as well.
eg.
select *
where msg='crash'
I have a rowno in there but I am pretty new to this stuff so not real sure.
Thanks in advance
TimPerhaps you can give us some DDL and show us what you want to retrieve when
you issue am smaple command
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
> Hi,
> I am trying to do a query where I want to show not only the relevant
> results
> but the previous line as well.
> eg.
> select *
> where msg='crash'
> I have a rowno in there but I am pretty new to this stuff so not real
> sure.
> Thanks in advance
> Tim
>|||ok, as I say I am pretty new to this so excuse any misunderstandings
columns: date msg type detail
rowno
11/5/06 info prog running
1
11/5/06 error prog crash
2
I basically want to be able to show the rowno previous to the prog crash
So I have,
Select *
from TABLENAME
where detail ='prog crash'
but I not sure how to do the next bit to retrieve the previous row.
thanks in advance
Tim
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> Perhaps you can give us some DDL and show us what you want to retrieve
when
> you issue am smaple command
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916000$0$4656$afc38c87@.news.optusnet.com.au...
>|||That would be one solution for that:
Select TOP 2 * from TABLENAME
Where Rowno <=
(
Select TOP 1 *
from TABLENAME
where detail ='prog crash'
order by rowno
)
Order by row no desc
I added the order by rowno if the detailcolumn is not precise enough,
perhaps mor ethan one coumn would fit that condition, saythose with 4 and
105, should then the rows 3 AND 104 be returned, or is the detail column
that precise only to return one row at a time ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
> ok, as I say I am pretty new to this so excuse any misunderstandings
> columns: date msg type detail
> rowno
> 11/5/06 info prog running
> 1
> 11/5/06 error prog crash
> 2
>
> I basically want to be able to show the rowno previous to the prog crash
> So I have,
> Select *
> from TABLENAME
> where detail ='prog crash'
> but I not sure how to do the next bit to retrieve the previous row.
> thanks in advance
> Tim
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:elnUUV1XFHA.1404@.TK2MSFTNGP09.phx.gbl...
> when
>|||yes would return 3 and 104 as well.
that query gives an error
'Only one expression can be specified in the select list when the subquery
is not introduced with EXISTS'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
> That would be one solution for that:
> Select TOP 2 * from TABLENAME
> Where Rowno <=
> (
> Select TOP 1 *
> from TABLENAME
> where detail ='prog crash'
> order by rowno
> )
> Order by row no desc
> I added the order by rowno if the detailcolumn is not precise enough,
> perhaps mor ethan one coumn would fit that condition, saythose with 4 and
> 105, should then the rows 3 AND 104 be returned, or is the detail column
> that precise only to return one row at a time ?
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
> news:42916a43$0$10304$afc38c87@.news.optusnet.com.au...
>|||Ok, I setup a sample with the Northwind database which can be ported easily
for your case, try it and raise a hand if you stuck into it.
Select * from Orders O
Where CustomerID = 'VINET'
UNION
Select * from Orders
Where OrderID in
(
Select OrderID-1 from Orders O
Where CustomerID = 'VINET'
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Timmeah" <timoth@.optushome.com.au> schrieb im Newsbeitrag
news:42917894$0$9263$afc38c87@.news.optusnet.com.au...
> yes would return 3 and 104 as well.
> that query gives an error
> 'Only one expression can be specified in the select list when the subquery
> is not introduced with EXISTS'
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ecvwdn1XFHA.1384@.TK2MSFTNGP09.phx.gbl...
>|||You missed the most basic idea of the Relational Model. Tables have no
ordering. Rows are not "lines" or "records". Those exists in file
systems. What does this "rowno" mean in your data model, and where
does it occur in the reality you are modeling?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.|||>> I am pretty new to this so excuse any misunderstandings
Quite often people confuse the two dimensional representation of a table on
some visual medium ( a sheet of paper or a computer screen ) with the rows
sequentially arranged as if they can be navigated through like with a
recordset.
However, one of the basic tenets of relational databases is that, there is
no essential order associated with the rows in a table -- i.e. a row is
identified by <table_name, key_value> each and every value is identified by
the triplet <table_name, column_name, key_value). If the values in your
database cannot be identified this way, as a general rule, your design is
flawed. Therefore asking something about "previous line" does not really
make sense.
As a more specific suggestion, please read www.aspfaq.com/5006. Provide
DDLs, sample data & expected results for others to reproduce your problem
scenario.
Anith