Database

swjam30-Mar-09 21:09

swjam

30-Mar-09 21:09

are you sure? someone answered differently from another (dbforums) forum.

----------------------------------------------------------
Lorem ipsum dolor sit amet.

Mycroft Holmes30-Mar-09 21:35

30-Mar-09 21:35

The somone else doesn't know what they are talking about.

Simple to test, create a table with 1 field, insert 2 records and then select from the records. They will ALWAYS be returned in the order they were inserted unless you order them.

Here you go

DECLARE @tbl TABLE (sField VARCHAR(20))

INSERT @tbl (sField) VALUES ('Z') 
INSERT @tbl (sField) VALUES ('A') 

SELECT * FROM @tbl

Never underestimate the power of human stupidity
RAH

ButtonMoon31-Mar-09 11:36

ButtonMoon

31-Mar-09 11:36

The first thing to clear up is that you cannot control the insertion order of rows using ORDER BY, so the assumption on which the question is based is inaccurate. This is well documented in Books Online and elsewhere (see refs below).

If you insert rows one at a time then it is true that the insertion order determines the allocation order represented by IAM pages. It will not determine the eventual ordering of data pages however, or even necessarily the logical ordering of rows within a page, especially if page splits subsequently occur.

In a query, there is no way to specify how rows should be sorted other than by using ORDER BY. This is an important feature because it allows the optimiser to choose the most efficient path to the data based on requirements. By leaving out ORDER BY you signal to the optimiser "I don't care about order" and the optimiser chooses an execution plan accordingly. Check this for yourself by looking at the execution plan of a query without ORDER BY. You will typically see a scan showing "Ordered=False". Depending on various factors the optimiser can choose different strategies to retrieve the data for an unordered scan, which may or may not mimic the order in which rows were first inserted. Some relevant factors that influence the actual plan and sorting are: fragmentation, covering indexes and whether an "Advanced" scan is used (Enterprise Edition only).

Of course it is easy to contrive examples where the query order matches the insertion order and just as easy to make up examples where it doesn't. Just as surely, those nice safe examples could return different results if the conditions under which they ran were changed in some subtle way.

The correct and documented behaviour is that the sort order of a query without ORDER BY is undefined. If you ever assume otherwise then you are in unsupported territory and you must decide for yourself whether it is worth the possible risk of leaving out ORDER BY.

Hope this helps.

References

Insertion order not guaranteed for SELECT INTO with ORDER BY:
http://msdn.microsoft.com/en-us/library/ms188385.aspx

Insertion order not guaranteed for INSERT with ORDER BY (SQL Engine Team Blog):
http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx

Advanced Scan:
http://msdn.microsoft.com/en-us/library/ms191475.aspx

Ordered and Unordered Scans:
Inside Microsoft SQL Server 2005 T-SQL Querying, Chapter 3
http://www.sql.co.il/books/insidetsql2005/

Queries without ORDER BY are unordered:
http://technet.microsoft.com/en-us/library/cc917540.aspx
http://msdn.microsoft.com/en-us/library/ms187956.aspx

Mycroft Holmes31-Mar-09 13:58

31-Mar-09 13:58

Thanks for that info - I have never actually dug under the hood of the way a db treats the rows as I also am of the opinion that I don't really care, if I need a sort order I will impose it.

Never underestimate the power of human stupidity
RAH

Ashfield31-Mar-09 1:30

Ashfield

31-Mar-09 1:30

Microsoft do not guarantee the order any records are returned unless you put an order by on the select. The default is to return the data in the prmary key order, but this cannot be relied on.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Reading Columns

ffowler30-Mar-09 10:00

ffowler

30-Mar-09 10:00

Greetings All,

I have a particular problem that I am hoping someone with some TSQL know-how can assist me with.

Here's the issue:
I have a table that contains 40 some-odd columns. the first 2 columns are essentially identifiers. The following columns are components of the identifier. To give a clear picture, lets' say the first row has CHCK in the 1st column and Chocolate Cake in the 2nd column. The rest of the columns contains the ingredients of the Chocolate Cake (let's say 7 ingredients). The next row is for Grilled Cheese. This row would only have 3 ingredients.

Here is what I would like to achieve:
I want to run through each row and read every column to see if there is a value in it. If there is a value, I would like to put the identifier and the ingredient in another table with an insert so that this new table (with only 2 columns) looks something like this
CHCK eggs
CHCK milk
GCHEES bread
GCHEESE cheese

Is there a way that I could do this in a stored procedure? I know that I could use a cursor to the rows but each row could have a variable number of *ingredients* and that is where I am lost.

Thank you in advance for any suggestions you may offer.

sam#30-Mar-09 20:52

sam#

30-Mar-09 20:52

please don't explain table data in words. Please show us that in tabular format so that we can have a clear steer view

Mycroft Holmes30-Mar-09 21:42

30-Mar-09 21:42

STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients

Where ingredients has a 1 to many relationship with the recipie field. If you are just starting then do some reading on data structures, database design before you continue. This is a typical clusterf**k created by someone who has no idea what they are doing. Do some research and get it right, otherwise you will waste a lot of your time attempting to undo these stupid structures.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson30-Mar-09 23:37

Jörgen Andersson

30-Mar-09 23:37

Mycroft Holmes wrote:
STOP - you are making me sick, this is the WORST design for a table you can possibly have. I STRONGLY suggest you chuck this table out and design a correct data structure. Something like:
Recipie
Ingredients

I think that is what he wants to do.

"Lots of programmers have had sex - some have even had it with members of the same species." - Pete O'Hanlon

anup keshari30-Mar-09 21:47

30-Mar-09 21:47

select column1,convert(varchar(20),isnull(column2,''))+''+convert(varchar(20),isnull(column3'')) as coluumn2 from tablename

use the above query and write like above for all the ingredent column .

lets me know if any things is needed.

anup keshari30-Mar-09 22:02

30-Mar-09 22:02

sorry for the above reply

use this

select coumun1,column2 from table1
where column2 is not null
union all

select coumun1,column3 from table1
where column3 is not null
union all

select coumun1,column4 from table1
where column4 is not null

here for three column u have to write for all column

anup keshari30-Mar-09 22:33

30-Mar-09 22:33

for sqlserver 2005

use unpivot method

Looking for a Web/SQL reporting subsystem

ffowler31-Mar-09 2:05

ffowler

31-Mar-09 2:05

Whoa, Whoa guys. I may not have explained myself clearly from the responses I have received.
The database tables are in the correct format as you have suggested. I need a query to pull the data in the format I mentioned to another party. I am not certain how they will be utilizing the data in this manner but that what has been spec'd out to me.

Thank you Anup for your assistance and I will further explore the command you have suggested.

Max Stayner30-Mar-09 9:47

Max Stayner

30-Mar-09 9:47

Hi,

I'm looking for a SQL-based reporting system with the following features for running against a SQL Server 2005 database.

- SQL for each report is to be hand coded (i.e. NOT 'ad-hoc/drag-n-drop end user reporting')
- Users will select parameters at run time
- Output to browser (don't care about controlling hard-copy formatting)
- Must be able to include complete T-SQL.
- Ideally include support for transactional operations

I wrote something very much like this in my last job, but would rather buy this time.

Anyone used or even know of such a beast? I've scoured Google with no result. Everything I've found is either 'Build reports without knowing SQL' or 'use this toolkit to integrate a specific report into your application.

Thanks,

Max.

Re: Looking for a Web/SQL reporting subsystem

Mycroft Holmes30-Mar-09 21:45

Summing Amount by Group field

30-Mar-09 21:45

It's called SQL Server Reporting Services. DevExpress also do an excellent reporting tool.

Never underestimate the power of human stupidity
RAH

BijayaSharma30-Mar-09 9:36

BijayaSharma

30-Mar-09 9:36

Hi There

I need to create report that needs to group by "[B]status[/B]= Funded and Not funded" , "[B]Institute[/B]" body is title

I need to find the [B]Total amount [/B] that Institute had earned for different Research Proposals. In One Proposal there could be Group of people involved

I am creating Report using this Query

SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.Amount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
WHERE (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));
My result is some thing like this

Title Inst Type Person Role SID Amount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000

B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000

c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000

I want the total By Inst = Del = 14,00,000
and Inst= comp = 1,200,000

I have created the group by Institute, added text box at the footer of Institute and assigned = Sum([Amount]) to the control source of that text box. I am not sure Why I am not getting the result

Thank you
Bijaya

Re: Summing Amount by Group field

Anubhava Dimri31-Mar-09 1:09

Anubhava Dimri

31-Mar-09 1:09

You are not Using Group By method.For your knowledge U should Use Group By Query.

Like : SELECT A.CustomerName, A.SUM(OrderPrice) FROM Sales as A GROUP BY A.CustomerName

Best Regard
Anubhava Dimri

mailto: anubhava.prodata@gmail.com

Re: Summing Amount by Group field

BijayaSharma31-Mar-09 4:19

BijayaSharma

31-Mar-09 4:19

Thank you Anubhava,

This is my Group By Query :

SELECT Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Sum(Proposal.Amount) AS SumOfAmount, Proposal.DateSubmitted
FROM ((Proposal LEFT JOIN Agency ON Proposal.PID = Agency.PID) LEFT JOIN (Institutions RIGHT JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID) LEFT JOIN (Contact RIGHT JOIN Role ON Contact.PersonID = Role.PersonID) ON Proposal.PID = Role.PID
GROUP BY Proposal.Title, Agency.AID, Proposal.Type, Role.PersonID, Role.RoleTypeID, Proposal.SID, Div.IID, Institutions.IName, Institutions.Regional, Proposal.DateSubmitted
HAVING (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.Regional)=Yes)) OR (((Role.RoleTypeID)=3 Or (Role.RoleTypeID)=4 Or (Role.RoleTypeID)=6) AND ((Proposal.SID)=1 Or (Proposal.SID)=2) AND ((Institutions.IName) Like "*TEES*" Or (Institutions.IName) Like "*COE*"));

Still result remains the same

Title Inst Type Person Role SID SumOfAmount
A Del ATE Lee PI Funded 500,000
A Del ATE Juli PI Funded 500,000
A Del ATE Kris Co-PI Funded 500,000

B Del NSF Kris PI Funded 900,000
B Del NSF Kris PI Funded 900,000
B Del NSF Kris co-PI Funded 900,000

c comp ATE Kris PI N-Funded 1,200,000
c comp ATE Kris PI N-Funded 1,200,000

At Institute footer cntains Text feild =sum(SumOfAmount) for Del= ($500,000 *3) + ($900,000 * 3) = $ 4200,000
for comp= $2400,000
Where As I want this
By Inst = Del = 14,00,000
and Inst= comp = 1,200,000

Thank you
Bijaya

Re: Summing Amount by Group field

BijayaSharma1-Apr-09 6:28

BijayaSharma

1-Apr-09 6:28

Hi Everybody,

I am working on the proble With the first query. I am not sure why I am not getting the Sum Amount by Institute(Inst)
Just to get Total Amount by Institute. I created another Query that is:

SELECT Institutions.IName,
Sum(Proposal.Amount) AS SumOfAmount,
Proposal.sid
FROM Proposal INNER JOIN (Institutions INNER JOIN Div ON Institutions.IID = Div.IID) ON Proposal.PID = Div.PID

GROUP BY Institutions.IName, Proposal.sid
HAVING (((Proposal.sid)=1 Or (Proposal.sid)=2));

Datasetview of the above Query is:

Inst SumAmount SID(status)
Del 14,00,000 funded
comp 12,00,000 not-funded

But when I assign this to Combo box at the Footer of Institute Group. I am not getting the accepcted result. I am getting this one.

Del = 500,000
Comp = 1200,000

Could onyone help me why this is happening? Confused | :confused:

I am counting on you, my project is due comming Monday. help Needed!

thank you
Bijaya Cry | :((

How do i display Values Horizonatally In Crystal Reports

Veerkumar Patil30-Mar-09 4:31

Veerkumar Patil

30-Mar-09 4:31

Re: How do i display Values Horizonatally In Crystal Reports

anup keshari30-Mar-09 4:58