Query

Hello friends!

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
6 Viswa 10700.0
3 Prabu 5000.0
4 Vikram 9000.0
9 Sachin 10000.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
4 Vikram 9000.0
3 Prabu 5000.0
6 Viswa 10700.0
5 Siva 1700.0
2 Vasu 7000.0
1 Raja 3000.0

Thanks & Regards
E.ElayaRajawithout using an ORDER BY? why not? that's the only way

rudy|||Originally posted by ThankuGod
Hello friends!

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
6 Viswa 10700.0
3 Prabu 5000.0
4 Vikram 9000.0
9 Sachin 10000.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
4 Vikram 9000.0
3 Prabu 5000.0
6 Viswa 10700.0
5 Siva 1700.0
2 Vasu 7000.0
1 Raja 3000.0

Thanks & Regards
E.ElayaRaja|||RE: Hello friends!

I have a row like this

Select * from SalMax
Sid Sname Salary
---- ------- ---------
1 Raja 3000.0
2 Vasu 7000.0
The Output I want is just the Reverse without using Order by any column
the Output I want is
15 Dravid 14000.0
9 Sachin 10000.0
Thanks & Regards E.ElayaRaja

Q1 The Output I want is [descending Salary] without using Order by
A1 Create a clustered descending index on Salary.|||My Question is not answered. I think u didnt undestand my question.
I repeat

I have a row like this

Select * from SalMax

Sid Sname Salary
---- ------- ---------
7 Raja 3000.0
2 Vasu 7000.0
5 Siva 1700.0
15 Dravid 14000.0

The Output I want is just the Reverse without using Order by any column

the Output I want is
15 Dravid 14000.0
5 Siva 1700.0
2 Vasu 7000.0
7 Raja 3000.0

I just want the reverse of the query .

Thanks & Regards
E.ElayaRaja|||Do you mean that you first run a query which results in a set of records, returned in no particular order at all - then, you would like to get that very same set of records just displayed in the reverse order?

May I ask what the problem behind is?

I guess you could select the records into a temporary table, to which you then add the clustered descending index?|||RE: My Question is not answered. I think u didnt undestand my question...
I just want the reverse of the query . Thanks & Regards E.ElayaRaja

Q1 [I think u didnt undestand my question]?
A1 True I did not, that was why it was restated and paraphrased.

Q2 [Given a set of arbitrary query results, (returned in no particular order): How would may one get that very same set of records returned in the reverse order, (BUT) without using ORDER BY?]

A2 The Coolberg post correctly alluded to the answer of Q2. Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.|||Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.

maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY

what the heck is wrong with using ORDER BY anyway?

rudy|||RE: quote:
------------------------
Insert the result set into a work table with an additional autoincrementing identity column. Then alter the work table to make the additional identity column clustered and descending. Finally, a select from the work table of all columns (except the additional identity column) should return the very same set of records, in the reverse order.
------------------------
maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY
what the heck is wrong with using ORDER BY anyway? rudy

Q1 [What the heck is wrong with using ORDER BY anyway?]
A1 The question being addressed excluded using ORDER BY [Given a set of arbitrary query results, (returned in no particular order): How would may one get that very same set of records returned in the reverse order without using ORDER BY?]

Q2 [Maybe it's me but the performance of the above strategy is going to be a lot worse than ORDER BY?]
A2 Not you, but rather the DBMS / query engine. I would expect avoiding using ORDER BY as a general practice to be a rather expensive endevour. (Perhaps the exclusion is related to some sort of performance testing?)|||Hello friends!

Thanks for answering me. Ur solutions are really useful.

Thanks & Regards
E.ElayaRaja

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?
I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>
|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>>I am trying to make a query to get the date and time a row and the date
>>and time from the next row:
>> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
>> Note_Text Batch_Text Lot_Text Author Batch_Note Line
>> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
>> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
>> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
>> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
>> This is what I came up with.
>> USE Iconics_Data
>> Select Source_TDate as 'Start Date'
>> From IHZONE1_Notes
>> where Batch_text='5' and Lot_Text='2443'
>> I get
>> Start Date
>> 2005-07-11 14:59:20.000
>> I also need the query to give me a column
>> End Date
>> 2005-07-11 15:00:50.00
>> How could I do this?
>sql

Query

I am trying to make a query to get the date and time a row and the date and
time from the next row:
Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs Note_Text
Batch_Text Lot_Text Author Batch_Note Line
0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
This is what I came up with.
USE Iconics_Data
Select Source_TDate as 'Start Date'
From IHZONE1_Notes
where Batch_text='5' and Lot_Text='2443'
I get
Start Date
2005-07-11 14:59:20.000
I also need the query to give me a column
End Date
2005-07-11 15:00:50.00
How could I do this?I got it wirking.
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
<Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>I am trying to make a query to get the date and time a row and the date and
>time from the next row:
> Source_Index Source_TDate Source_MSecs Note_TDate Note_MSecs
> Note_Text Batch_Text Lot_Text Author Batch_Note Line
> 0 7/11/2005 14:59 816 7/11/2005 14:59 816 5 2443 LotTag 1 1
> 0 7/11/2005 15:00 636 7/11/2005 15:00 636 5 9987 LotTag 1 2
> 0 7/11/2005 15:01 211 7/11/2005 15:01 211 6 BatchTag 1 3
> 0 7/11/2005 15:14 736 7/11/2005 15:14 736 7 BatchTag 1 4
> This is what I came up with.
> USE Iconics_Data
> Select Source_TDate as 'Start Date'
> From IHZONE1_Notes
> where Batch_text='5' and Lot_Text='2443'
> I get
> Start Date
> 2005-07-11 14:59:20.000
> I also need the query to give me a column
> End Date
> 2005-07-11 15:00:50.00
> How could I do this?
>|||A little typo:
Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
From IHZONE1_Notes N1, IHZONE1_Notes N2
Where N1.line=N2.line + 1 and N2.Batch_text='5' and N2.Lot_Text='9987'
"Chris Gay" <ChrisGay@.RMTWoodworth.com> wrote in message
news:ekWcBC0hFHA.328@.tk2msftngp13.phx.gbl...
>I got it wirking.
> Select N2.Source_TDate as 'Start Date', N1.Source_TDate as 'End Date'
> From IHZONE1_Notes N1, IHZONE1_Notes N2
> Where N1.line=N2.line + 1 and N1.Batch_text='5' and N1.Lot_Text='9987'
>
> <Chris Gay> wrote in message news:upPlYhyhFHA.4048@.TK2MSFTNGP10.phx.gbl...
>

Querring only non NULL row/column

Hi,
I want to query few rows from a table. I don't want to get a row, where
certian column has a NULL value.
How can I do that?select * from table
where field is not null
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:0DAA7CB9-1BF4-4091-A7B0-FCA90581060E@.microsoft.com...
> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT * FROM Table
WHERE column IS NOT NULL
"mavrick101" wrote:

> Hi,
> I want to query few rows from a table. I don't want to get a row, where
> certian column has a NULL value.
> How can I do that?|||SELECT ...
FROM YourTable
WHERE col_x IS NOT NULL
David Portas
SQL Server MVP
--

Queries dependent on results from other datasets.

Hi,

I need data from two datasources. From the first datasource a get only one row. Then I need to use one of the values in this row to filter the next query. I couldn't make it to work. Is it possible? Suggestions on how to do this?

Tomsi

One possible solution is to create internal parameter that will take default value from 1st data source.

When you can use this parameter in the second data set.

quastion about triggers

hi,

I have a few quastions about triggers:

1) I want that to run a specific function or exe, after a row update.

How can I run a function via the trigger?

2) Is ther any trigger that for an updae of a culomn (not a row)?

thanks,

moria

Hi Moria,
First of all, there are differences id you implement the trigger on a CLR basis (2005) or on a TSQL basis (<2005), so next time, try to give some information about your system, that the proposed solution will better fit your needs.
"How can I run a function via the trigger?"
You talked about executing a EXE, therefore you cann call the extended procedure SP_CMDSHELL which is able to execute a program via the command line (More information about that can be found in the BOL)
COMMENT: It′s odd to execute a command in a trigger. Trigger bahve synchronously, while executing the trigger other people using the data won′t be able to change the lock data (depends on your locking, worst case would be table locking). If there is an error in your command (or a long running command), like waiting for input or something, the trigger will never come back, so you have to
keep that in mind.

"Is ther any trigger that for an updae of a culomn (not a row)?"
Well, yes and no. A trigger is fired per statement (not per ROW) and regardless if your "watched" column changed or not, but if you are in the trigger you have the possibility to use the
IF UPDATE(SomeColumn)
DoSomething
But the trigger is fired regardless, if the column changed or not, keep that in mind, if you have more than this statement in your trigger.
HTH, Jens Suessmeyer.

|||

first of all, thank you for the detailed answer.

I am working with SQL 2000, and .NET 2005.

>>"More information about that can be found in the BOL"

what's BOL, and where can I find it?

>>we understand that an exe can block the SQL, but we want to know when a user updates a table so that we will run a certain program to take the new data, analyze it and reload it to another table. if not by an exe, how do you recommend to do it?

>>In case of deleted a column, is the trigger fired? which one?

thanks a lot,

moria.

|||

Hello again,

"what's BOL, and where can I find it?"
http://www.aspfaq.com/show.asp?id=2229

If this is an external program you sure have to use the exe, but what about wrting a entry in a table that something changed on the program running on an at basis or as a service checks the data, does its recalculation (or whatever) and marks the process as done in the table ? Thats are designing question which cannot be decided on the basis of the only few information given.

HTH, Jens Suessmeyer.