Database

26-Jul-11 0:45

I have a table in Sql Server structured something like this:

RowId  OrderId  OldOrderId  Type  Example Data  
8787   1        NULL        I     The first order in sequence 1
9837   2        1           U     The second order in sequence 1
10838  3        2           U     The third order in sequence 1
11873  4        3           U     The fourth order in sequence 1
12554  5        NULL        I     The first order in sequence 2
14113  6        5           U     The second order in sequence 2
15003  7        6           U     The third order in sequence 2
18343  8        7           U     The fourth order in sequence 2

What I am trying to do is find the last record in each sequence using only the columns I have in the table. I cannot add anything to group by as I am not in control of the data. Has anyone got any ideas? I have done something like this before but I cannot seem to put my hands on it at the moment.

Thanks in advance

Simon_Whale26-Jul-11 2:22

Simon_Whale

26-Jul-11 2:22

Does the solution have to be SQL Based?

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

Adam Jasper26-Jul-11 2:32

26-Jul-11 2:32

Yes, I need it to be SQL based. I think I'm nearly there, think possibly being the operative word Smile | :)

Blue_Boy26-Jul-11 2:35

26-Jul-11 2:35

I hope this will help you

select   max(mt.rowid) as RowId,

(select top 1 mt1.Data from mytable mt1 where rowid = max(mt.rowid)) as Data
from mytable mt
group by substring(mt.data, charindex('order',mt.data),len(mt.data)) 
order by max(mt.rowid) desc

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

Adam Jasper26-Jul-11 2:39

26-Jul-11 2:39

Thanks for the reply, unfortunately there is nothing to group by. I need to find the last in sequence using only the OrderId and OldOrderId values.

Blue_Boy26-Jul-11 2:41

26-Jul-11 2:41

Based on that data of table which you have gave in your first post then query will work. Otherwise post data structure how they are in table.

by this query

select   max(mt.OldOrderID) as OldOrderID,

(select top 1 mt1.Data from mytable mt1 where mt1.OldOrderID = max(mt.OldOrderID)) as Data
from mytable mt
group by substring(mt.data, charindex('order',mt.data),len(mt.data)) 
order by max(mt.OldOrderID) desc

you will get this result

OldOrderID	Example Data<br />
7	The fourth order in sequence 2<br />
3	The fourth order in sequence 1

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

Adam Jasper26-Jul-11 3:16

26-Jul-11 3:16

Sorry, the data in ExampleData column could be anything, just purely an example to indicate that the record has additional data.

Blue_Boy26-Jul-11 3:29

26-Jul-11 3:29

Ok, how about to explain you more your case, which result you want to get from your table data?

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

Adam Jasper26-Jul-11 3:33

26-Jul-11 3:33

The resultset you had in your original post is the resultset I am looking for, however, like I said in my original post, there is nothing to group by. What I probably should have posted is given the following dataset, how do I find the last RowID in the sequence and missed off the ExampleData column.

Adam Jasper26-Jul-11 3:48

Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() interrupts execution of the whole program [modified]

26-Jul-11 3:48

For anyone who is interested, here is my solution:

SELECT 
	Orders.ExampleData
FROM 
	OrderSeq AS Orders
LEFT JOIN
	OrderSeq AS Updates ON (Orders.OrderId = Updates.OldOrderId) AND (Updates.[Type] = 'U')
where 
	(Updates.RowId IS NULL)

I knew it was easy, just couldn't get my head around it this morning.

Thanks to everyone who replied.

NandoMan25-Jul-11 11:51

NandoMan

25-Jul-11 11:51

Hello people of CP,

I have a problem and would like you to help me fix it.

The problem is that right after I call a stored procedure calling the method stated in the title, the execution of the program is interrupted without any warning or prompt whatsoever.

The code block is this:

public virtual object Execute()
      {         return this.command.ExecuteNonQuery();
      }

I know the command executes because the state in the DataBase is changed. But right after the return this.command.ExecuteNonQuery(); line, the program just stops. It doesn't throw an exception, it doesn't even go to the last bracket of the method, the program just exits.

So, any idea what this might be?

Thanks a lot!

Edit:

Here's a clearer example:

private void SetSystemEnviroment()
        {
            try
            {
                using (OracleCommand command = this.con.CreateCommand())
                {
                    command.CommandText = "SetSystemEnviroment";
                    command.CommandType = CommandType.StoredProcedure;

                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

Again, after the command.ExecuteNonQuery(); line, it just stops the execution of the program, no warnings, no exceptions, nothing.

modified on Monday, July 25, 2011 6:23 PM

Re: Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() interrupts execution of the whole program

Shameel31-Jul-11 7:25

Shameel

31-Jul-11 7:25

Can you post the SetSystemEnviroment Stored Procedure code?

Re: Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() interrupts execution of the whole program

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

Herman<T>.Instance

1-Aug-11 3:52

I miss the Command.Connection.Open() before you executeNonQuery()

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

Single row for multiple results

vanikanc22-Jul-11 7:54

vanikanc

22-Jul-11 7:54

Hello,

I would like to show data as a single row instead of multiple rows.

For example, a certain user has two status associated, like maybe "Pending Approval" and "Waiting Email Verfication". Instead of showing these statuses on two different rows, I would like to show as one row.

Currently,
John Smith Pending Approval
John Smith Waiting Email Verfication

Should be
John Smith Pending Approval, Waiting Email Verfication

There could such multiple states for this user, but all show in one row.

Thanks so much for your time!

Blue_Boy22-Jul-11 8:20

22-Jul-11 8:20

You can use C# code to do it. Or do you need to do it only in SQL side?

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

vanikanc22-Jul-11 8:27

vanikanc

22-Jul-11 8:27

SQL - MS SQL.

Thanks!

Blue_Boy22-Jul-11 8:28

22-Jul-11 8:28

Can you post structure of your table? I guess your table contain unique column?

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

www.cacttus.com

Tim Carmichael22-Jul-11 8:28

Tim Carmichael

22-Jul-11 8:28

You can either build your state list directly in a stored procedure, or create a function that will build it for you.

I encountered a similar situation yesterday: for a given item, multiple sources of data.

I created a function to concatenate the items, comma delimited, and used the function in the query to return all of the data in a single row.

Tim

vanikanc22-Jul-11 8:42

vanikanc

22-Jul-11 8:42

My apologizes.

This link worked perfectly,

<a href="http://www.sqlservercentral.com/scripts/Miscellaneous/32004/">http://www.sqlservercentral.com/scripts/Miscellaneous/32004/</a>[<a href="http://www.sqlservercentral.com/scripts/Miscellaneous/32004/" target="_blank" title="New Window">^</a>]

, using COALESE

Shameel22-Jul-11 9:00

Shameel

22-Jul-11 9:00

This should help.

SQL

DECLARE @Col1 varchar(max)

SELECT @Col1 = COALESCE(@Col1 + ', ', '') + CAST(Col1 AS varchar(100))
FROM Table1
WHERE (................)

SELECT @EmployeeList

PIEBALDconsult23-Jul-11 14:07

PIEBALDconsult

23-Jul-11 14:07

You can write your own aggregates with CLR Stored Procedures.

CLR Stored Procedure and Creating It Step by Step[^]

http://msdn.microsoft.com/en-us/library/ms190790.aspx[^]

But I don't recommend it.

Niladri_Biswas24-Jul-11 22:37

Niladri_Biswas

24-Jul-11 22:37

Assuming Sql Server 2005 and above, try this

Declare @t table([Name] varchar(20),[Status] varchar(50))
insert into @t values
('John Smith','Pending Approval'),('John Smith','Waiting Email Verfication')
--,('User2','Pending Approved'),('User2','Sent email'),('User2','Other activity')
--Select * from @t

select Result = [Name] + '  ' +  Stuff((Select ',' + CAST([Status] as varchar(100)) from @t t2 where t1.[Name] 
= t2.[Name]  for XML Path('')),1,1,'')
from @t t1
group by t1.[Name] 

/*
Result
------
John Smith  Pending Approval,Waiting Email Verfication
*/

Niladri Biswas