Return The Data From A Table Ordered By Its Hierarchy
May 3, 2006
How can I create a function that returns hierarchical data from a table with this structure:
- CategoryID
- CategoryName
- CategoryFather
I want to bring the result set like this...
CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2
How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.
Thanks.
View 9 Replies
ADVERTISEMENT
Nov 5, 2012
I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.
Specifically, something like:
Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"
I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.
(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).
View 6 Replies
View Related
Feb 27, 2008
Hi,
I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..
Table Structure and Sample Data
Id ParentId Name Code DisplayOrder
1 null Group 1 G00001 1
2 null Group 2 G00002 2
3 1 Sub-Group 1 SG0001 1
4 2 Sub-Group 2 SG0002 1
5 3 Sub-Sub-Group 1 SSG001 1
6 null Group 3 G00003 3
7 3 Sub-Sub-Group 2 SSG002 2
Desired Result
Id ParentId Level Name ExtendedName DisplayOrder
1 null 1 Group 1 Group 1 1
3 1 2 Sub-Group 1 Group 1 -> Sub-Group 1 1
5 3 3 Sub-Sub-Group 1 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1 1
7 3 3 Sub-Sub-Group 2 Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2 2
4 2 2 Sub-Group 2 Group 1 -> Sub-Group 2 1
2 null 1 Group 2 Group 2 2
6 null 1 Group 3 Group 3 3
View 11 Replies
View Related
Sep 3, 2013
I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this
Id-Hreid
Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get
Emp3-Emp2
Emp3-Emp1
Emp2-Emp1
View 2 Replies
View Related
Jan 9, 2014
I need to Return values with hierachy sum.
I have provided the tables with data and result expected below
============
CREATE TABLE
============
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] ....
The parent data will have either QA or QC
Child will have data in QY.
========================
EXPECTED RESULT
========================
Mode_Info |Mode_Detail| QA| QC|QY
Air||4 | |
Sea|||5|
SEA|SHIP|||15
SEA|BOAT|||25
ROAD|||2 |
ROAD|BUS|||20
ROAD|BIKE|||30
ROAD|CYCLE|||4
ROAD|TRAM|||10
ROAD|CAR|3||
ROAD|BMW|||36
ROAD|AUDI|||84
View 3 Replies
View Related
Oct 21, 2013
I am wanting to run a SQL statement whereby i return the ID of any employee's Director.
The database for employees has a reports to field which enables me to see the hierarchy of managers above any employee.
There is also a IsDirector flag that indicates a director.
So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.
eg if A reports to B and B reports to C (who is a director) then it returns C.
I basically want the script to run until a director is found.
how would i do this?
View 5 Replies
View Related
Sep 13, 2000
I need to export data in a specific order (e.g. the order in which the data was inserted into the table). My BCP was doing this then just started exporting in random order. I know that SQL 7 doesn't guarantee return order unless specified by an ORDER BY clause. However, that's not possible with BCP unless you use the command line BCP with the QUERYOUT option. That's my problem. I'm using the BulkCopy object in DMO and I don't see an option to order the data or use a query with an order by statement. Any suggestions?
--Buddy
View 1 Replies
View Related
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 Replies
View Related
Apr 18, 2007
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
View 2 Replies
View Related
Aug 11, 2015
I am working on a model where I have a sales fact table. Each fact record has four different customer fields (ship- to, sold-to, payer, and bill-to customer). I have one customer dimension table that joins to the sales fact table four times (once for each of the customer fields above). When viewing the data in Excel, I would like to have four hierarchies (ship -to, sold-to, payer, and bill-to customer) within Customer.Â
Is there a way to build hierarchies within my Customer dimension based on the same Customer table? What I want is to view the data in Excel and see the Customer dimension. Within Customer, I want four hierarchies.Â
View 2 Replies
View Related
Jul 23, 2005
I have the following query:SELECT Month, Sum(Hits) AS Hits FROM tblHits GROUP BY Month ORDER BYMonthUnfortunately it only returns rows for months that have data assignedto them.How can I tweak this so that months 1-12 are returned, and Hits = 0 formonths with no data in the base table?Thanks.
View 2 Replies
View Related
Apr 13, 2007
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
View 3 Replies
View Related
Sep 24, 2007
I have two table, tblCharge and tblSentence, for each charge, there are one or more sentences, if I join the two tables together using ChargeID such as:
select * from tblCharge c join tblSentence s on c.ChargeID=s.ChargeID
, all the sentences for each charge are returned. There is a field called DateCreated in tblSentence, I only want the latest sentence for each charge returned, how can I do this?
I tried to create a function to get the latest sentence for a chargeID like the following:
select * from tblCharge c join tblSentence s on s.SentenceID=LatestSentenceID(c.ChargeID) but it runs very slow, any idea to improve it?
thanks,
View 4 Replies
View Related
Dec 12, 2007
How can I get the data from a table that stores hierarchical information keeping the right order and hierarchy levels?
I also need to specify a first level....let me explain.
Table: Sites
Id (PK)
Title
Url
ParentId
If I have something like this:
1 | Test | http://localhost | NULL
2 | Sub-Test | http://localhost/subtest | 1
3 | SecondItem | http://anotherServer | NULL
4 | ChildOfSub-Test | http://localhost/subtest/child | 2
And I specify that I want all child items below the Id 2, it must return:
Id | Title | Url | ParentId | Depth
2 | Sub-Test | http://localhost/subtest | 1 | 0
4 | ChildOfSub-Test | http://localhost/subtest/child | 2 | 1
How can I do this? I've saw some examples with CTEs, but they always return all of the items.
View 7 Replies
View Related
Oct 26, 2007
Hi, with the three tables below, I want to add relationships so that each Grandparent can spawn many parents and each Parent can spawn many children.
I keep getting an error when I try to connect them. Could I get some help creating that hierarchy please?
Thanks in advance!
Here are the tables:
CREATE TABLE [dbo].[GrandParent] (
[GPID] [int] IDENTITY (1, 1) NOT NULL ,
[GPName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Parent] (
[PID] [int] NOT NULL ,
[GPID] [int] NOT NULL ,
[PName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Child] (
[CID] [int] NOT NULL ,
[PID] [int] NOT NULL ,
[ChName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
--PhB
View 7 Replies
View Related
Oct 11, 2007
My problem is that my:hustyp)[1] only fetches the first occurace of this tag. In my xmlfile this field is a repeating table created in infopath 2007. How do I manage to get the rest dynamically.
------XML-file---------------------------------------
<my:group1>
<my:group2>
<my:hustypTF>5</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>6</my:hustypTF>
</my:group2>
<my:group2>
<my:hustypTF>7</my:hustypTF>
</my:group2>
</my:group1>
-----------------------------------------------------
------T-SQL for fetching data from XML datatype------
WITH xmlnamespaces('http://schemas.microsoft.com/office/infopath/2003/myXSD/2007-01-15T13:29:33' AS my)
SELECT FormData.value('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp)[1]', 'varchar(99)') AS IdFastBet
FROM MinaDekl
CROSS APPLY FormData.nodes('(/my:xxx/my:Formular/my:group1/my:group2/my:hustyp) as TempTab(testTab1)
-----------------
View 2 Replies
View Related
Dec 28, 2007
I've got a table that includes:CREATE TABLE [dbo].[Content] ( [Id] int IDENTITY(1, 1) NOT NULL, [ParentId] int NULL,
I'm wanting to make sure that a ParentId must be in the table as Id someplace else. When I try to do it by making it a foreign key get the error:
--------------- SQL ---------------
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [dbo].[Content] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
---------- ERROR MESSAGE ----------
There are no primary or candidate keys in the referenced table 'dbo.Content' that match the referencing column list in the foreign key 'Content_fk3'.Could not create constraint. See previous errors.
Any ideas?
ALTER TABLE [dbo].[Content]ADD CONSTRAINT [Content_fk3] FOREIGN KEY ([Id]) REFERENCES [Content].[dbo] ([ParentId]) ON UPDATE NO ACTION ON DELETE NO ACTIONGO
View 3 Replies
View Related
Jan 21, 2015
I have a table named 'DepartmentItem' which is designed with hierarchy structure. The column 'ParentId' from table DepartmentItem indicates parent-child relationship and department root among records. I have written and run a user-defined function I use recursive approach, but the function runs slowly.
My question: is there a better way to query that hierarchy table instead of using recursive?
** The current user-defined function that is written using recursive:
CREATE FUNCTION dbo.fnGetDepartmentTree
(
@departmentItemId int
)
RETURNS TABLE
AS
RETURN
with DepartmentItemTree(DepartmentItemId , DepartmentItemTypeId , ParentId, ItemOrder, Level)
[code].....
** And definition of table 'DepartmentItem' :
DepartmentItemId int IDENTITY(1,1) NOT NULL,
ParentId int NULL, -- Each department root starts when this column is NULL or the current row is department root. If it is not NULL then the current row has ParentId whose record has DepartmentItemId = ParentId of the current row (see more below)
IsActive bit NOT NULL DEFAULT ((1)),
[Code] .....
View 2 Replies
View Related
Apr 4, 2015
I have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.
CREATE TABLE [dbo].[t_prod_cat](
[cat_node_id] [bigint] IDENTITY(1,1) NOT NULL,
[advertiser_id] [bigint] NOT NULL,
[cat_hid] [hierarchyid] NULL,
[level] AS ([cat_hid].[GetLevel]()) PERSISTED,
CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED
(
[cat_node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.
Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"
CREATE TABLE [dbo].[t_prod_cat_work](
[temp_cat_node_id] [bigint] NOT NULL,
[temp_cat_hid] [hierarchyid] NOT NULL,
[advertiser_id] [bigint] NOT NULL
) ON [PRIMARY]
What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?
View 2 Replies
View Related
Nov 10, 2014
How to use hierarchy id data type in Bill of material database.
The msdn example [URL] ....
View 1 Replies
View Related
May 22, 2008
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
View 16 Replies
View Related
Aug 15, 2013
how to find the orphaned value from the below parent/child hierarchy Table.
create table dbo.Hier(parent varchar(100), child varchar(100))
insert into Hier
select 'subramanium','Manickam' union all
select 'subramanium','Munuswamy' union all
select 'Munuswamy','senthil' union all
select 'Munuswamy','sasi' union all
select 'Munuswamy','uma' union all
select 'manickam','vijay' union all
select 'manickam','bhavani' union all
select 'manickam','dhanam' union all
select 'uma','varsha'
Delete from Hier where child='uma'
I tried:
select parent from Hier
where parent not in(select Child from Hier)
and parent <> 'subramanium'
Getting resultset as:
parent
======
uma
I need to know whether my select statement is correct or not,if its correct,how to write the same in CTE?
View 2 Replies
View Related
Jul 23, 2005
Hi SQL GurusCould anyone please explain how the following stored procedure can beamended to retrieve Subordinates in alphabetical order ?The example below simply retrives records in the order in which theywere entered.It sounds very easy but I can't sort it out ?The following code was taken from Narayana Vyas Kondreddi's website(http://vyaskn.tripod.com/index.htm)Consider the employee table of an organization, that stores all theemployee records. Each employee is linked to his/her manager by amanger ID.CREATE TABLE dbo.Emp(EmpIDintPRIMARY KEY,EmpNamevarchar(30),MgrIDintFOREIGN KEY REFERENCES Emp(EmpID))GOCREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)GOINSERT dbo.Emp SELECT 1, 'President', NULLINSERT dbo.Emp SELECT 2, 'Vice President', 1INSERT dbo.Emp SELECT 3, 'CEO', 2INSERT dbo.Emp SELECT 4, 'CTO', 2INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6INSERT dbo.Emp SELECT 12, 'Tester 1', 11INSERT dbo.Emp SELECT 13, 'Tester 2', 11INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7INSERT dbo.Emp SELECT 18, 'Tester 3', 17INSERT dbo.Emp SELECT 19, 'Tester 4', 17INSERT dbo.Emp SELECT 20, 'Tester 5', 17GOCREATE PROC dbo.ShowHierarchy(@Root int)ASBEGINSET NOCOUNT ONDECLARE @EmpID int, @EmpName varchar(30)SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNameSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)WHILE @EmpID IS NOT NULLBEGINEXEC dbo.ShowHierarchy @EmpIDSET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root ANDEmpID > @EmpID)ENDENDGOEXEC dbo.ShowHierarchy 1GO---President------Vice President---------CEO---------CTO------------Group Project Manager---------------Project Manager 1------------------Team Leader 1---------------------Software Engineer 1---------------------Software Engineer 2------------------Test Lead 1---------------------Tester 1---------------------Tester 2---------------Project Manager 2------------------Team Leader 2---------------------Software Engineer 3---------------------Software Engineer 4------------------Test Lead 2---------------------Tester 3---------------------Tester 4---------------------Tester 5
View 3 Replies
View Related
Feb 10, 2015
I have been trying to convert an existing table that used adjacency list model (parentid,childid) to a table that use hierarchy Id type. So early on, I notice my original data does contains multiple roots. So I took a step to create dummy nodes to ensure all nodes fall into a single root structure. Another important fact is that each child node can have multiple parents.
My original source table contains 22461 records, when running the query below step 2 produces explosive number of records around 175,000+ records. I spent hours study the result and couldn't understand what actually causing this, I ran it against small set of test data I didn't seem the issue caused by child with multiple parents.
select * from SourceTable -- produces 22461 records
--step 1: first, get row number of child records in each parent
SELECT ChildID,ParentID, ROW_NUMBER() OVER (PARTITION BY PARENTID ORDER BY PARENTID) as Num
INTO #RelationshipTmp
FROM SourceTable;
[Code] ....
View 1 Replies
View Related
May 28, 2015
I'm attempting to load some data into an explicit hierarchy in MDS 2012 via the staging table and struggling with the HierarchyName field. Specifically I'm loading data into stg.[Entity Name]_Consolidated and using the exact name of the explicit hierarchy I've set up in the front end web application.
Originally my hierarchy was labelled "Reporting Hierarchy" and when loading the data into staging using this name then running the batch from the Import Data screen I can see the error message "Error - The HierarchyName is missing or is not valid.". I've checked the table mdm.tblHierarchy and can see that the name there is exactly as it was in the staging table and have since renamed the hierarchy as "Reporting_Hierarchy" with the same results.
View 0 Replies
View Related
Aug 31, 2012
Out of nowhere my derived hierarchies starting showing the following message in the MDS UI
No Level Defined:Â This Derived Hierarchy is incomplete......
As you can see below the structure is defined and still in tact. This message shows up in by the Explorer & System Admin areas. I'm also able to query the subscription view setup without any issue.
This is with 2012 w/ no CUs. Same setup in running in another environment without issue.
View 5 Replies
View Related
Jun 8, 2015
I have a time dimension which has Date, Week, Month and Year. However, the hierarchy will have only Week, Month and Year. It works great for any Sales measure with AggregateFunction as SUM.
I have created a new measure with AggregateFunction = LastNonEmpty. Also in the backend, I have pushed all the inventory data to last date in every month as inventory is always looked on a monthly basis not on a weekly basis. This measure shows correct data for every last week of the month in the hierarchy. However, Months and Years are displayed as zeros.
what I am doing wrong.
View 8 Replies
View Related
Jul 18, 2015
I am working on a query to generate parent child hierarchy from a table.
Table has below records.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
--===== Create the test table with
CREATE TABLE #mytable
[Code] ...
how to achieve this.l tried with temp tables it doesn't work.
View 5 Replies
View Related
Jun 25, 2007
Hello Experts. You may have more luck at this than me.
I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.
Example of how data looks like
I am looking for transactions like orderid 3 and 5.
OrderID
Product
1
hotdog
1
burger
1
taco
2
burrito
2
snack
2
chips
3
burger
4
hotdog
4
burger
4
taco
5
burrito
6
snack
6
chips
When i run
SELECT product, count(orderid)
From Table
Where BusinessDateID = 20060725
group by product
having (count(orderid)=1)
I only get back items that were only sold once.
I am looking for a result that looks like this
Product
Ordered alone
hotdog
2
burger
3
taco
4
burrito
32
snack
12
chips
76
View 7 Replies
View Related
Jul 24, 2006
hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance
View 1 Replies
View Related
Jan 31, 2008
Hi all,
I feel like I'm missing something really simple here...
I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")
I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.
Here's the code I'm using:
CREATE PROCEDURE GetCountries AS
SELECT Country_Name
FROM Countries
WHERE Country_Name = 'USA'
UNION
SELECT Country_Name
FROM Countries
WHERE Country_Name <> 'USA'
GO
I also tried selecting into a temp table and doing a UNION that way, but got the same results.
View 3 Replies
View Related
Jul 23, 2005
We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?
View 3 Replies
View Related
Jan 18, 2008
I€™m trying to follow examples from: http://www.sqlmag.com/Articles/ArticleID/49240/49240.html?Ad=1 for an update statement that needs to run in a specified order.
Here€™s my problem:
I have a table:
TopicActivity
PK €“ DayNum int (this is a date in YYYYDDD format)
PK €“ TopicId int
Visits int
LifetimeVisits int
LifetimeVisits is a new column that I want to calculate the value using the previous Visits column data.
Sample Data:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 0
2008002 2 1 0
2008002 1 3 0
2008003 1 10 0
I want the end result to look like this:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 5
2008002 2 1 1
2008002 1 3 8
2008003 1 10 18
Here is the query I€™ve been trying:
WITH ordered AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY TopicId ORDER BY DayNum) AS RowNum
FROM TopicActivity
)
update ordered set
ordered.LifetimeVisits = ordered.Visits +
isnull((select o2.LifetimeVisits
from ordered as o2
where o2.TopicId = ordered.TopicId
and o2.RowNum = (ordered.RowNum - 1)),0)
The update doesn€™t seem to be happening in order as LifetimeVisits seems to have random results every time I run the query. (Usually the first couple of rows are right. I€™m dealing with thousands of records) I can€™t use a cursor as the query takes way to long.
I'm running SQL Server 2005.
View 6 Replies
View Related
