July 30, 2012 at 2:51 am
Hey all,
I have this query
SELECTa.Report_Menu_Code ,
a.Menu_Name,
b.Sub_Menu_Code,
b1.Sub_Menu_Name,
b.Report_Code,
c.Report_Name,
c.Report_Path,
c.Report_url,
c.Group_Id,
d.Group_name,
e.ADLogon,
f.DisplayName,
f.BusinessUnit,
f.Branch,
f.Role,
(SELECT COUNT(DISTINCT(b2.Sub_Menu_Code)) FROM dbo.tblMI_Reports_SubMenus b2 WHERE b1.Report_Menu_Code = b2.Report_Menu_Code) AS SubMenu_Count
FROMdbo.tblMI_Reporting_Menusa
LEFT JOIN dbo.tblMI_ReportMenub ONa.Report_Menu_Code = b.Report_Menu_Code
LEFT JOINdbo.tblMI_Reports_SubMenusb1 ON b.Report_Menu_Code = b1.Report_Menu_Code
ANDb.Sub_Menu_Code = b1.Sub_Menu_Code
LEFT JOIN dbo.tblMI_Reportsc ON b.Report_Code = c.Report_Code
LEFT JOIN dbo.tblMI_Reporting_Groupsd ONc.Group_Id = d.Group_Id
LEFT JOIN dbo.tblMI_Users_Groupse ON d.Group_Id = e.Group_Id
LEFT JOINdbo.tblMI_Usersf ON e.ADLogon = f.ADLogon
Now I am trying to replace the above code. Now i have built some new tables which do more and have more logical relationships.
However i have one problem. The above query returns results in what looks like a random order. Its not random i know, but i cant see the order. I know that it can come off as how its stored physically on disk.
Now here is the problem - i need to have a row_number added to this query, so i can transfer this order to a new table (i am going to have a field used for ordering). But I cant do row_number as i dont know the order by fields.
Any ideas what i can do?
Many thanks
Dan
July 30, 2012 at 3:12 am
danielfountain (7/30/2012)
Hey all,Its not random i know, but i cant see the order.
Because there isn't one. Without ORDER BY there's no guarantee of the sequence of the data. It may be produced in the same sequence for the next ten years and then one day it will change, or it may be in a different sequence every time.
Personally I wouldn't worry about about trying to preserve the sequence of something that doesn't have a sequence.
Edited for my lousy spelling.
July 30, 2012 at 3:14 am
Hi Daniel
Without an ORDER BY, SQL Server will return the results in whatever order they fall out of the execution plan. If the plan changes - and there are many circumstances which can do this, the output order of the results could change too.
Figure out the current sort order by scrutinising the result set, then test it by adding an ORDER BY based on your findings.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2012 at 3:56 am
Thanks for the explainations.
Unfortunately i do have to worry about it. Obviously the execution plan hasnt changed in a while as people have got used to the order. Its one of those weird things, that i think this unordered set is incorrect - however the end users are used to the incorrectness and it would surprise them if it were changed.
In this query there is a where clause that i omitted. It effects the execution plan - so affects the order.
The only way i managed to get this to work was to run the query for each group (where clause) for which there is about 10. Then in excel make up a update statement to put the right order.
Not clean - but it worked.
Thanks all
D
July 30, 2012 at 3:58 am
danielfountain (7/30/2012)
Now here is the problem - i need to have a row_number added to this query, so i can transfer this order to a new table (i am going to have a field used for ordering). But I cant do row_number as i dont know the order by fields.
What do you mean by "But I cant do row_number as i dont know the order by fields."??....
You can Select the data into a Derived Table using this query and then add a Row Number to each row based on the Column according to which you want the Data to be Ordered By.
Knowing or not knowing the Column names according to which the Data is ordered in this Query would not matter if you would do it this way.
Edit: Sorry, missed your post there. Even if you have a Where Clause you still have a column in mind according to which you want the data to be Ordered....right?
You just need to Select the Data using this Query as a Derived table and then add a Row Number(ie: write the Row_Number() query on the Derived Table).
The Where clause would filter the data but the Order would still be maintained.
July 30, 2012 at 4:08 am
danielfountain (7/30/2012)
Thanks for the explainations.Unfortunately i do have to worry about it. Obviously the execution plan hasnt changed in a while as people have got used to the order. Its one of those weird things, that i think this unordered set is incorrect - however the end users are used to the incorrectness and it would surprise them if it were changed.
In this query there is a where clause that i omitted. It effects the execution plan - so affects the order.
The only way i managed to get this to work was to run the query for each group (where clause) for which there is about 10. Then in excel make up a update statement to put the right order.
Not clean - but it worked.
Thanks all
D
It's most likely that the output order more or less matches the cluster key of one of the tables in the query. It wouldn't take you long to test. Then you've nailed it.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2012 at 4:11 am
vinu512 (7/30/2012)you still have a column in mind according to which you want the data to be Ordered....right?
Nope - thats exactly the problem. I have an order i would have written this with. However the person who did write it a few years ago didnt put an order by clause on. So they come out the order that the execution plan pushes them out (and it seems the execution plan hasnt changed so has the result set hasnt either). The end users now have got used to seeing what they are looking for at position X on the list. Even though this seems quite random 🙂
The problem is now i am rebuilding these tables and one of the criteria i have is that the order can be changed by admins but at the moment it needs to stay as is.
So i need a new column that is used for ordering, that i need to have the order that the execution plan was pushing them out in! I havent been able to work out what order this is - as it seems random.
July 30, 2012 at 4:16 am
Doesn't the execution plan give you any clues as to which index it is using?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 30, 2012 at 4:21 am
Phil Parkin (7/30/2012)
Doesn't the execution plan give you any clues as to which index it is using?
Thats what i was hoping - but still cant see it. Dont worry - if not i have a manual work around.
July 30, 2012 at 4:26 am
Try adding this as a column, and see if it orders 'correctly'. If so, try select into and see if it's still ordered 'correctly'
ROW_NUMBER() OVER (ORDER BY (SELECT 1)) As PseudoOrder
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 4:33 am
danielfountain (7/30/2012)
Phil Parkin (7/30/2012)
Doesn't the execution plan give you any clues as to which index it is using?Thats what i was hoping - but still cant see it. Dont worry - if not i have a manual work around.
Post the plan here as a .sqlplan file attachment. Far better to do the job properly than have some dodgy workaround.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2012 at 4:44 am
GilaMonster (7/30/2012)
Try adding this as a column, and see if it orders 'correctly'. If so, try select into and see if it's still ordered 'correctly'ROW_NUMBER() OVER (ORDER BY (SELECT 1)) As PseudoOrder
That works perfectly as far as i can see - thank you very much. Just need to work this into some code now.
Ta
Dan
July 30, 2012 at 4:59 am
GilaMonster (7/30/2012)
Try adding this as a column, and see if it orders 'correctly'. If so, try select into and see if it's still ordered 'correctly'ROW_NUMBER() OVER (ORDER BY (SELECT 1)) As PseudoOrder
Yes, Exactly.
That's what I've been saying.
Select the Data as a Derived Table and add Row_Number() as follows:
Select *, RowNumber() Over (Order By a.Report_Menu_Code) As rn From
(SELECTa.Report_Menu_Code ,
a.Menu_Name,
b.Sub_Menu_Code,
b1.Sub_Menu_Name,
b.Report_Code,
c.Report_Name,
c.Report_Path,
c.Report_url,
c.Group_Id,
d.Group_name,
e.ADLogon,
f.DisplayName,
f.BusinessUnit,
f.Branch,
f.Role,
(SELECT COUNT(DISTINCT(b2.Sub_Menu_Code)) FROM dbo.tblMI_Reports_SubMenus b2 WHERE b1.Report_Menu_Code = b2.Report_Menu_Code) AS SubMenu_Count
FROMdbo.tblMI_Reporting_Menusa
LEFT JOIN dbo.tblMI_ReportMenub ONa.Report_Menu_Code = b.Report_Menu_Code
LEFT JOINdbo.tblMI_Reports_SubMenusb1 ON b.Report_Menu_Code = b1.Report_Menu_Code
ANDb.Sub_Menu_Code = b1.Sub_Menu_Code
LEFT JOIN dbo.tblMI_Reportsc ON b.Report_Code = c.Report_Code
LEFT JOIN dbo.tblMI_Reporting_Groupsd ONc.Group_Id = d.Group_Id
LEFT JOIN dbo.tblMI_Users_Groupse ON d.Group_Id = e.Group_Id
LEFT JOINdbo.tblMI_Usersf ON e.ADLogon = f.ADLogon) As a
July 30, 2012 at 5:02 am
danielfountain (7/30/2012)
That works perfectly as far as i can see - thank you very much. Just need to work this into some code now.Ta
Dan
Just bear in mind that's a trick, it's not good practice, it's not always going to behave consistently, it's prone to the same problems as ordering without an order by.
Use it once to get a persistent column that you can then order by (via insert into or select into), not as a permanent addition to code.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 30, 2012 at 5:02 am
danielfountain (7/30/2012)
GilaMonster (7/30/2012)
Try adding this as a column, and see if it orders 'correctly'. If so, try select into and see if it's still ordered 'correctly'ROW_NUMBER() OVER (ORDER BY (SELECT 1)) As PseudoOrder
That works perfectly as far as i can see - thank you very much. Just need to work this into some code now.
Ta
Dan
Sorry but it doesnt order by a.Report_Menu_Code. That does not give the correct results.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply