Hi all,
I faced a problem, I have two tables - part and partmaster
part : part_no, part_qty (no key)
partmaster : part_no, part_description (primary key : part_no )
I want to select table part.* and partmaster.part_description.
(run on mssql 2k)
select a.*, b.part_description
from part a, partmaster b where a.part_no *= b.part_no
I want to and expect to have the result order like table "part". However, after the join, the order is different. I try to run it on mssql 7.0, the order is ok.
Then I modify and run the statement select a.* from part a, partmaster b where a.part_no *= b.part_no on 2k again. The result order is ok.
can anyone tell me the reason?
Now I try to fix this problem is adding a sequence field "part_seq" into table "part" and run the statement by adding a order by part_seq.
It does work!
I was just messing around with some ad hoc views and table returningUDFs today so I could look at and print out data from a small tableand noticed something strange.If I stick my select statement into a View the columns are returned inthe order I specify in the SELECT, but if the same statement is in a UDF(so I can specify a parameter), the columns are not returned in theorder specified in statement.I know that relations don't have a specified column order, but it was myunderstanding that a SELECT statement could be used to define how youwant your data presented. Views seem to respect the order specified inthe SELECT, but functions don't.What am I missing? Is there some way to force the order of the columnsreturned from a SELECT?View:CREATE VIEW dbo.View1ASSELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt,ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE '4.2.%')Column order from this view:Ident, Text, Type, ParentStmt, ForStmt, IfStmt, ChildStmt, ThenStmt,ElseStmt, NextStmtFunction:ALTER FUNCTION dbo.Function1(@SearchPrm varchar(255))RETURNS TABLEASRETURN ( SELECT Ident, Text, Type, ParentStmt, ForStmt, IfStmt,ChildStmt, ThenStmt, ElseStmt, NextStmtFROM dbo.tblStmtWHERE (Ident LIKE @SearchPrm) )Column order from this function:Type, Text, ElseStmt, NextStmt, IfStmt, ChildStmt, ThenStmt, Ident,ParentStmt, ForStmtTable:(I know that this table isn't entirely normalized, but it serves mypurposes to have a matrix instead of a fully normalized relation):CREATE TABLE dbo.tblStmt (StmtID INT IDENTITY(1,1) CONSTRAINT PK_Stmt PRIMARY KEY,Ident VARCHAR(255),Text TEXT,ErrorText TEXT,Type INT,ParentStmt VARCHAR(255),ChildStmt VARCHAR(255),IfStmt VARCHAR(255),ForStmt VARCHAR(255),ThenStmt VARCHAR(255),ElseStmt VARCHAR(255),NextStmt VARCHAR(255),FullName VARCHAR(255),LocalName VARCHAR(255),Method INT)INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.1', 'LineNumberOfResp := EMPTY' 64, '4.2', '4.2.2')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.2', 'FOR K:= 1 TO 2', 128, '4.2', '4.2.3','4.2.7')INSERT INTO tblStmt Ident, Text, Type ParentStmt, ChildStmt, ForStmt,NextStmtVALUES('4.2.3', 'Person[K].KEEP', 16, '4.2', '4.2.3.1', '4.2.2', '4.2.4')INSERT INTO tblStmt Ident, Text, Type, ParentStmt, NextStmtVALUES('4.2.3.1' 'AuxInterviewerName := DOSENV', 64, '4.2.3', '4.2.3.2')
Hello group!i have a table "group_code" wich relates the names ofnt-(domain)-groups to codes. now i want use the stored procedurexp_logininfo (asking for the group-membership of the current user) tojoin the result to "group_code". then i must use the new result (thecode) to join against other tables.i know now, that i cant join results of SPs against tables. may be thata UDF with a table result is the correct approach. but i have no ideahow to wrap the xp_logininfo in a UDF.a other way can be to do in a UDF the same thing like the xp_logininfo.then this UDF should deliver at least the nt-(domain)-groups in wichthe current-user is a member.Is there anybody who can give me the code for that?many thanx in advance.Karl
WHERE Tag.TagID = JobTag.TagID AND Image.JobID = Job.JobID AND Job.JobID = JobTag.JobID AND TagCat.TagCatID = Tag.TagCatID
ORDER BY Image.ImageID, Tag.SortOrder
I'd like to replace the query above with a some kind of a join, but I'm not sure how to do it. The reason I want to do this is to only get the category (TagCat.name) one time for each set of tags that go under that category for that particular job. Any thoughts on how I could do this? Would I need to create more than one query or do you think this can be one with just one query?
I am using SLQ Server 2008 R2. The database was designed by another company.
I have two tables: Client and Client_Location. In the Client table the pk is Client_ID. There is also a unique key: sys_Client_ID. Both the Client_ID and the sys_Client_ID fields exist as a foreign keys in the Client_Location table. However, the fields are not noted as unique in the Client_Location table. There are two fields in the Client_Location table that determine when the address was effective. They are from_dt and end_dt.
Multiple records have been loaded into the Client_Location table to track old as well as current addresses of clients.
I'm trying to run a report that will pull clients with a plan_id constraint from the Client table and join the Client_Location table to retrieve the current address of these clients.
My SQL is:
select distinct (a.client_id), a.cli_last AS Last_Name, a.cli_first AS First_Name, a.cli_middle AS Mid_Init, b.city AS City, b.county AS County, b.state AS State from ECBH.dbo.tbl_Client a inner join ECBH.dbo.tbl_Client_Location b on a.client_id = b.client_id inner join ECBH.dbo.tbl_client_insurance c on a.client_id = c.client_id inner join ECBH_TEST.dbo.tbl_GEF_County d on b.county = d.COUNTY_NAME where c.plan_id = 4 order by a.cli_last, a.cli_first
Because multiple records exist in the Client_Location table, the result set has duplicates. How can I pull only the results where the from_dt is most recent?
So if an item is a bottle, it will have a unique BottleKey and Null for CaseKey and if an item is a Case, it will have a unique CaseKey and Null for BottleKey. However, the PrimaryIDs are the same for both the bottle and case. I get the results to look like this:
I have an problem with the order of the results after a join.
My first query works fine and the order of field Name ist correct.
Select * FROM (SELECT * FROM dtree A1 WHERE A1.Subtype=31356 AND A1.DataID IN (select DataID from dtreeancestors where AncestorID=9940974)) t
When I do a join the order of the left table changes
Select * FROM (SELECT * FROM dtree A1 WHERE A1.Subtype=31356 AND A1.DataID IN (select DataID from dtreeancestors where AncestorID=9940974)) t, llattrdata A4 WHERE t.DataID = A4.ID
How can I do a join and keep the order of the left table?
The queary I need is to display the result in this form. must be like this
docIDdocName Avaragerating 1aaa3 2bbb3 3ccc0
NOTE : For getting the average I used this queary “SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount FROM tbl_Rating WHERE tbl_rating.docID = tbl_documents.docID”
I have a criteria where i want to join table 1 with table 2 , table 1 consists of products which were given to salesman to sell and table 2 has the sales data which salesman has sold out. Now i want to know left over products of each sales with join .Below is my data, here is what i am trying to do, but it return only salesman 1 data.
CREATE TABLE Salesman_Product ( SalesManID int, ProductID int ) INSERT INTO Salesman_Product (SalesManID,ProductID) Values (1,1),(1,2),(1,3),(1,4) Â INSERT INTO Salesman_Product (SalesManID,ProductID) Values (2,1),(2,2),(2,3),(2,4)Â
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
I have a function that is designed to return a variable that containsconcatenated values from a partinular field in the returned rows:DECLARE @output varchar(8000)SELECT@output =CASEWHEN @output IS NULL THEN CAST(TSD.ScheduledTime ASvarchar(4))ELSE @output+ ', '+ ISNULL(CAST(TSD.ScheduledTime ASvarchar(4)),'')ENDFROM TSDWHERE ClientGUID = 2000001447020001 ANDParentGUID = 6000006684068001Select @outputThe variable returned with this code contains:"1200, 1400, 1200, 1400"I want to only get the unique values so that the variable returns "1200,1400". Seems simple enough just to add DISTINCT to the SELECT statement.However, what is returned is simply "1400".I cannot figure out why that is the case. Is there any explanation to thisresult?Side note: I can work around this by using a cursor but I would like toknow why DISTINCT does not work.Many thanks in advance for any help that can be provided!Pat
/* Test table */ create table test (c1 char(1), c2 varchar(1)); insert into test values ('','');
/* Query */ select c1, len(c1) len_c1, c2, len(c2) len_c2 from test
The result of the len(c1) expression is 0. I would expect the correct result to be 1, since "c1" is a fixed-length character string type and the values are right-padded with spaces to fit the defined length, in this case 1.
when i try the following SQL batch, I get a result-set which is not order by datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.
who can help me?thanks in advance ... select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark INTO #fifo_temp from ##stuff_fifo UNION select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP') ORDER BY out_date
DROP TABLE ##stuff_fifo select * from #fifo_temp
the following can get a correct result:
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark from ##stuff_fifo UNION select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP') ORDER BY out_date
I am using SQL 2005 merge replication with SP1 hotfix build 9.00.2227.00. This build is in use rather than SP2 because a fix I need is not yet available for SP2
Essentially the problem is as follows: 1) Initial state is that merge replication of table and views is working fine 2) I then alter one view which references a new view in the same publication 3) Synchronization processes the view scripts in the wrong order regardless of the processing order 4) An 'invalid object name' error results as the new view has not arrived at the subscriber when alteration of the first view is attempted
The number suffixes on the script filenames in the snapshot folder do, however,appear to be numbered correctly so as to process in the correct order
Note that I have tried using the default processing order and have also set the processing order explicitly using sp_changemergearticle - but the problem still occurs
I have tried to recreate this problem on a small database with a minimum of articles, but attempts at repro have failed to date with a simple configuration - ie the processing order applied is correct
Hi, I have this problem on Reporting Services 2005 SP2:
There is a stored procedure that is the source of a dataset in report, this procedure return a recordset ordered by some fileds (es. order by fields1, fields2, ecc...). This procedure also have some parameters, but this isn't important. If I launch the stored procedure in sql server management studio the data are returned in the correct order, instead, when I run the report, the data are showed in wrong order.
select row_number() over (partition by merrickid order by recorddate asc) as rn, merrickid, recorddate, allocestgasvolmcf, sum(allocestgasvolmcf) over (partition by merrickid order by recorddate asc) as xxxxxxxx from dbo.completiondailytb where merrickid=1965
I get the following message after I insert the sum() over function:
Msg 102, Level 15, State 1, Line 5 Incorrect syntax near 'order'.
Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.
This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.
I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.
So; if you read my earlier thread here (http://www.dbforums.com/showthread.php?t=1214353), you'll know that I'm trying to build stored procedures to deal with ticketing queries, and that it's all getting a bit complicated. I have, however, made a bit of progress and now have the following working:
SELECT [tblTickets].[id] AS ticketId, [tblEvents].[id] AS eventId, [tblStands].[id] AS standId, [tblAdmissionDates].[id] AS admitDateId, [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities]. AS minBookingQuantity, [tblBookingMaxQuantities].[booking_quantity] AS maxBookingQuantity, MIN([tblQuotas].[quota]) AS Quota, [B]SUM([tblBasket].[ticket_quantity]) AS History, [tblTickets].[price], [tblTickets].[availability]
FROM [tblTickets] LEFT JOIN [tblEvents]ON [tblEvents].[id] = [tblTickets].[event_id] LEFT JOIN [tblStands]ON [tblStands].[id] = [tblTickets].[stand_id] LEFT JOIN [tblBookingDates]ON [tblBookingDates].[id] = [tblTickets]. LEFT JOIN [tblTicketConcessions]ON [tblTicketConcessions].[id] = [tblTickets].[ticket_concession_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMinQuantities ON [tblBookingMinQuantities].[id] = [tblTickets].[booking_min_quantity_id] LEFT JOIN [tblBookingQuantities] AS tblBookingMaxQuantitiesON [tblBookingMaxQuantities].[id] = [tblTickets].[booking_max_quantity_id] LEFT JOIN [tblAdmissionDates]ON [tblAdmissionDates].[id] = [tblTickets].[admission_date_id] LEFT JOIN [tblMemberships]ON [tblMemberships].[id] = [tblTickets].[membership_id] LEFT JOIN [tblQuotas]ON ([tblQuotas].[event_id] = [tblTickets].[event_id] OR [tblQuotas].[event_id] IS NULL) AND ([tblQuotas].[stand_id] = [tblTickets].[stand_id] OR [tblQuotas].[stand_id] IS NULL) AND ([tblQuotas].[admission_date_id] = [tblTickets].[admission_date_id] OR [tblQuotas].[admission_date_id] IS NULL) AND ([tblQuotas].[concession_id] = [tblTickets].[ticket_concession_id] OR [tblQuotas].[concession_id] IS NULL) AND ([tblQuotas].[membership_id] = [tblTickets].[membership_id] OR [tblQuotas].[membership_id] IS NULL) AND ([tblQuotas].[ticket_id] = [tblTickets].[id] OR [tblQuotas].[ticket_id] IS NULL) [B]LEFT JOIN [tblBasket] ON [tblBasket].[ticket_id] = [tblTickets].[id] LEFT JOIN [tblOrders] ON [tblOrders].[id] = [tblBasket].[order_id]
WHERE 1=1 AND ([tblTickets].[ticket_open] = 1) AND (([tblEvents].[id] = @eventId OR @eventId = 0)AND ([tblEvents].[event_open] = 1)) AND (([tblStands].[id] = @standId OR @standId = 0)AND ([tblStands].[stand_open] = 1)) AND (([tblAdmissionDates].[id] = @admissionDateId OR @admissionDateId = 0)AND ([tblAdmissionDates].[date_open] = 1)) AND ([tblTicketConcessions].[id] = @concessionId OR @concessionId = 0) AND ((getdate() BETWEEN [tblBookingDates]. AND [tblBookingDates].[booking_end_date]) OR ([tblBookingDates].[booking_start_date] IS NULL AND [tblBookingDates].[booking_end_date] IS NULL)) AND (([tblMemberships].[id] IN (SELECT [membership_id] FROM [tblUsers_Memberships] WHERE [user_id]=@userId)) OR [tblMemberships].[id] IS NULL) [B]AND ([tblOrders].[user_id] = @userId OR @userId=0)
GROUP BY [tblTickets].[id], [tblEvents].[id], [tblStands].[id], [tblAdmissionDates].[id], [tblEvents].[event_name], [tblStands].[stand_name], [tblTicketConcessions].[concession_name], [tblMemberships].[membership_name], [tblAdmissionDates].[admission_start_date], [tblAdmissionDates].[admission_end_date], [tblBookingMinQuantities].[booking_quantity], [tblBookingMaxQuantities].[booking_quantity], [tblTickets].[price], [tblTickets].[availability] GO
Except... there's two problems with it. One is that it only returns tickets that you've already bought, the other is that it doens't work out correctly how many of those tickets you've bought in past orders.
Both the orders in there are for the same user id: "1". All the tickets in there are tied to event "2".
When I run Exec dbo.getAvailableTickets 2,0,0,0,1, it tells me it's found 6 of ticket 5, 4 of ticket 6 and 12 of ticket 14. And I can't for the life of me figure out how it's calculating it. Any ideas?
And how do i get it to return all tickets regardless of whether you've bought them previously or not?
Hi, I have a query written in SQL 2000 which returns incorrect result. The query uses left outer join and a view. I read an issue related to this in one of microsoft bug report in this article http://support.microsoft.com/kb/321541.
However, there's a slight difference in the sympton second bullet wherein instead of a expression the query returns a fixed string for one of the column value.
Although the issue mentioned in article seems to be fixed. The later one still seems to be reproducible even with Service Pack 4. However, this issue doesn't appear in SQL Server 2005.
Here's the query to reproduce this error.
Code Snippetcreate table t1 (pk1 int not null,primary key (pk1)) create table t2 (pk1 int not null,label1 varchar(10) not null,primary key (pk1)) go insert into t1 values (1) insert into t2 values (2, 'XXXXX') go create view V as select pk1, 'ZZZZ' as label1 from t2 go select A.pk1 as A_pk1, B.pk1 as B_pk1, B.label1 as B_label1 from t1 as A left outer join V as B on A.pk1 = B.pk1 go
This query is similar to the one mentioned in the article except that in the SELECT clause of CREATE VIEW statement I am passing a fixed value for column "label1".
I just want to confirm that this is an issue and no fix is available for this so far.
Hello! I have two similar servers (hardware) with SQL Server 2000 (SP1) on Win2000 (SP2). (sp_helpsort - default)
When I run the same SELECT statement from Query Analyzer on each machine I get different rows order in result Grid (the order of rows stays the same for each machine but different for machine1 and machine2)
SELECT table1.a, table1.b, table1.c, table1.d
FROM table2, table1, table3
WHERE table2.nTestDefnId = 1034 AND table2.nDefnId = table1.nDefnId AND table3.szClinDiscType = 'X' AND table2.nDiscId = table3.nDiscId
What is a reason for such behavior and how I can fix it?
I have an issue while display the result in the required order. How to get the required output.
Code :
USE tempdb GO
IF OBJECT_ID('tempdb..#VersionFormat_tbl') IS NOT NULL DROP TABLE #VersionFormat_tbl CREATE TABLE #VersionFormat_tbl ( [FormatID] [smallint] NOT NULL, [Description] [varchar](50) NULL,
[Code] ....
Present output : fileExtension FormatID Description fileExtension versionFormatTypeId txt 1 Text txt 1 html 2 HTML html 1 xml 3 XML xml 1 pdf 4 PDF pdf 1 xls 5 Excel xls 1 doc 6 Word doc 1
the number of results per CaseID are varable some have 1 record others up to 36 records..I need to pivot the results based off of the competed date Name has 20 distinct values but we can have diplicates...so something like
Case Name Date 1 This 2015-01-01 1 That 2015-01-03 2 That 2013-01-01 2 Then 2014-12-03 2 This 2015-01-01 2 There 2015-04-03 3 This 1999-01-01 3 That 2001-01-02 3 This 2006-01-01
looks like this
1 This That 2 That Then This There 3 This That This
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code: ID Type Name Field 1 Type1Name1(group1) 2 Type2Name2(group2) -group2 3 Type3Name3(group3) 4 Type4Name4(group4) 5 Type5Name5(group2) -group2 6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
As you can see, some Divisions have no correspondents in Doc, I want to show the count(1) result as 0 for those Division, and output the result in the order by DivID
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.
I used right or left join it gives all dates value but when i select particular date i get only the punched details.
Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.
Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?
Information: I'm using SQL server 2012
create table datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into datatable values
