How To Keep Original Order When Querying A Table?

Sep 10, 2004

Hello, everyone:

I have a table like:

ColName
b
b
b
d
d
d
a
a
c
c
c

I use DISTINCT to filter duplicated row. I want to get the return by original order like:
b
d
a
c

However, SQL Server re-order it if using DISTINCT and return like:
a
b
c
d

Can any one have the idea to handle that? Thanks

ZYT

View 4 Replies


ADVERTISEMENT

How To Select Data From Excel Table By Original Column Order?

Feb 21, 2007

Hello, everyone:

I have an Excel table that has columns like,

Vendoe_Name
Level
Address
Email
Phone

I use the statemen

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C: est.xls;Extended Properties=Excel 8.0')...[Sheet1$]

and got output by column order,

Address
Email
Level
Phone
Vendoe_Name

How to make output by original column order like,

Vendoe_Name
Level
Address
Email
Phone

Any help will appreciated. Thanks

ZYT

View 1 Replies View Related

How Can I Use SELECT DISTINCT And Maintain The Original Order

Apr 26, 2007

Say I have a result set with two fields numbers and letters.

1 A3 A1 B2 B


 The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet

it will reorder the new result set by the Number field and return

123

 However, I'd like maintain the Letter order and return

132

View 1 Replies View Related

Trying To Save Editted Textbox Value In Table But Original Value Saves Instead - Trouble With Table Update Query

Jan 9, 2008

This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.

View 1 Replies View Related

SQL Server 2008 :: Add A Trigger That Inserts Original Data From 1 Table To Another

Apr 10, 2015

I am trying to create a trigger on a table. Let's call it table ABC. Table looks like this:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABC](
[id] [uniqueidentifier] NOT NULL,

[Code] ....

When someone updates a row on table ABC, I want to insert the original values along with the current date and time getdate() into table ABCD with the current date and time into the updateDate field as defined below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ABCD](
[id] [uniqueidentifier] NOT NULL,

[Code] .....

The trigger I've currently written looks like this:

/****** Object: Trigger [dbo].[ABC_trigger] Script Date: 4/10/2015 1:32:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[ABC_trigger] ON [dbo].[ABC]

[Code] ...

This trigger works, but it inserts all of the rows every time. My question is how can I get the trigger to just insert the last row updated?

I can't sort by uniqueidentifier in descending as those can be random.

View 9 Replies View Related

T-SQL (SS2K8) :: How To Introduce New Select / Join From Another Table Without Duplicating Original Data

Feb 19, 2015

I built a query that brings in 'Discounts' (bolded) to the Order detail by using the bolded syntax below. I started off by running the query without the bolded lines and got exactly what I was looking for but without the ‘Discount’ column. When I tried to add the ‘Discount’ into the query, it duplicated several order lines. Although total ‘Discount’ column ties out to the total amount expected in that column, ‘Total Charges’ are now several times higher than before.

For example, I get 75 records when I run without the bolded syntax and I get several hundred results back when adding back in the bolded syntax when i should still be getting 75 records, just with an additional column ‘PTL Discount’ subtotaled.My question is, how to I introduce a new select or join from another table without duplicating the original data?

select
first_stop.actual_departure ‘Start'
, last_stop.actual_departure 'End'
, last_stop.city_name 'End city'
, last_stop.state 'End state'
, last_stop.zip_code 'End zip'

[code]....

View 9 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

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?

View 8 Replies View Related

T-SQL (SS2K8) :: Pivot Query - Convert Data From Original Table To Reporting View

Apr 8, 2014

I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.

Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14

View For Reporting:

Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId

Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14

View 6 Replies View Related

Datamining Model Automatically Add Space To Field Name. Can't Use Prediction Join With Original Table

Aug 30, 2007

Dear All,


I have a database table which has all the inputs, key and the result. In visual studio, I created a decision tree model which has exactly the same fields as in the table. However the visual studio automatically add space preceding the capital letters. As the field name in the Datamining model and those in the database table are slightly different. I cannot use NATURAL prediction join. Is there anyway to told the visual studio not to add the spaces in the variable names?

Thanks and regards,

Tony Chun Tung Siu

View 1 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

Transact SQL :: Querying Data From Master Table Into Transaction Table?

Oct 13, 2015

I am stuck in the following scenario.

Tbl_Loan
Trans_ID
Emp_ID
Guarantor_1_ID
Guarantor_1_ID
TRN_01
EMP_01

[code]....

View 5 Replies View Related

Querying Using Another Table

Apr 30, 2008

Hello all,

I have a table (tbl_a) with 2 columns: itemNames | Date .
ItemNames can have duplicates.
I have another table (tbl_b) with date ranges in 2 columns: startDate | endDate.

what I would like to do is: query tbl_a where date between startDate and endDate of tbl_b.

does anyone have an idea?
thanks in advance.

View 2 Replies View Related

Querying Table More Than Once

Jun 12, 2008

Hi All

Thx in advanced for any help anyone can offer me.

I have 2 tables Staff and Class which are as follows

Staff
ID (Primary Key)
FirstName
MiddleName
Surname
Etc. . .

Class
ID (Primary key)
LessonTemplateID
TeacherID
TermID
AssistantID

LessonTemplateID and TermID link to other tables which i don't need right now.

The Class.TeacherID and Class.AssistantID both link to the Staff.ID

I am trying to create a query where i can say i want to select a class.ID of lets say 12 and display both the Teachers name and the Assistants Name.

If i do just one join between Staff.ID and lets say Class.TeacherID all is fine and i can display the teacher.FirstName for a particular class.ID. However my problem is when im trying to display both teacher and assistant. Every time i try to create a query to do this the query comes back blank.

Im really stuck on this and just can't figure it out (is it even possible?).

Thx for any help Scott.

View 2 Replies View Related

Querying Between Table Adapters ?

Sep 12, 2006

Hi all, I am currently moving from access queries to SQL and am using the query Builder In Visual web developerto help me with the SQL Code..This has been easy while just querying the one table adapter but Now I am stuck..I have a table adapter that links to an SQL Server database through a connection in web.config.I have another table adapter that links to DB2 through a different Connetion in web.config.My question is ..How do you query from one table adapter to another.Each table adpater can only query based on the 1 connection ? Thx,Ray..

View 1 Replies View Related

Log Users Querying Table

May 17, 2000

We have been asked to keep a log of users querying a particular table.
User's can query any odbc compliant tool, not just one application.

Can we do this w/ sql server 7?

View 1 Replies View Related

Querying The Size Of Table Fields?

Jun 5, 2008

Hello I am writing a stored procedure and instead of "hard coding" the size of the procedure parameters I would like to set them to the size of fields in my database.
 for example instead of declaring a parameter like this:
@ProjectDescription varchar(500)
 
I would like to do something like this:
 
@ProjectDescription varchar(Select size of "ProjectDescription" From Projects);
 
Thanks for the help! 

View 4 Replies View Related

Querying Table In Arbitrary Database

May 1, 2006

Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.

What I've tried:

DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'

SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1

Result:
Invalid object name '@DBName.dbo.UPR00100'.

I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.

Thanks for the help
Wes

View 2 Replies View Related

Querying A Really Badly Designed Table

Mar 28, 2006

I am working on an application with a number of tables (which I can't
change) that have data of the form a1, b1, c1... a2, b2, c2...a3, b3,
c3... The tables contain 5 to 10 sets of data in each row.



I want to create a stored procedure to return a cursor with a row for
each set of data for a row of the table. I know I can use unions:



select a1 as a, b1 as b , c1 as c... from sometable where pk=@pk

union

select a2, b2, c2... from sometable where pk=@pk

union

select a3...



Is there a way that is more efficient (eliminates requerying the table and all the unions)?

View 4 Replies View Related

SQL Query Question- Querying The Middle Table In A M To M

May 17, 2000

I need help with a query, and I haven't seen anything like this in a SQL book.

I will use the Northwind database for this example. The tables I am using are Products, Order_Detail, and Orders. There is a many to many relationship between Products and Orders, that is resolved by the Order_Detail table. Suppose I want to find out which products are related to each order. How would I query the tables?

Thanks everyone,
Nathan

View 1 Replies View Related

Querying Single Table For Multiple Summaries - How?

Jul 20, 2005

Folks,While I still have some hair left, can someone help me with thisquery?I have a table "TestRunInfo". Amongst other fields there are"TestRunIndex" (Pri Key), "TesterID", "Duration", and "Status".The Status field links to a Status table, which links the index valueto a more meaningful label "Pass", "Fail" etc...As you may have guessed, there is a record for each test that anindividual tester runs, and with that record is a duration, and status(1,2,3 etc).What Im trying to do, is create a datasheet view, with a single rowfor each testerID, summarising that Testers work as follows:TesterID, Total Duration, Count of passed tests, Count of failed testsSo far I have:Select TesterID, sum(Duration), count(Status) FROM TestRunInfo GROUPBY TesterIDBut this of course purely gives the total number of tests run by thatengineer as the count. I need to break it down. Help? Someone?Please?!?!?TIASteve

View 2 Replies View Related

Transact SQL :: Is Table Will Be Extracted When Querying To DB Views

Sep 15, 2015

I wonder how the query will be executed when querying to views.Is database engine will be extract the tables that are used to create the view and query for the result ?

View 5 Replies View Related

Data Warehousing :: Querying In Fact Table

May 2, 2015

I have a Fact Table with a ID column as Primary key and clustered index is created. And also I have 4 dimensions FK's of data type INTEGER. And finally, I have one aggregation measure in the Fact Table.

Now, my situation is How can I improve the speed of querying the fact table by creating any of the below indexes?

1. XML
2. Spatial
3. Clustered
4. Non-Clustered

View 2 Replies View Related

SQL Server 2012 :: Querying Table With Several Date Type Columns

Oct 30, 2014

I have a table (we will cal DateTable) with several (20) columns, each being a date type. Another table's (Project) PK is referenced in the DateTable.

I am trying to write a query that will pull all dates for a specific project from the DateTable if they meet certain criteria(i.e. if the date is <= 7 days from now.

I started with a normal select statement selecting each column with a join to the project and then a where clause using

(DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()) OR (DateTable.ColumnName BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE())) ...

The rest of the columns(all with OR between them).

The problem with this is that because I am using OR once one of the dates meets the criteria it selects all the dates that are associated with the project. I ONLY want the dates that meet the criteria and don't care about the rest.

Obviously because I have all the columns in the select statement... So I need something like

Select ALL Columns
from DateTable d
Join Project p
where p.ProjectID = d.ProjectID AND only dates BETWEEN GETDATE() AND DATEADD(day, 7, GETDATE()))

View 2 Replies View Related

Query A SQL Server Compact Table While Querying A SQL 2005 Server Table

May 21, 2008

Hi there,

I'm trying to run a query on a SQL Server 2005 table which has a WHERE clause that requires a query from my SQL Compact table.



SELECT * from RemoteDB.TESTDB.dbo.Objects

WHERE Last_Updated > '2008-05-21 10:51:00'

AND Object_PARENT IN (select Object_CODE from LocalDB.PDADB.dbo.Objects)


Basicallly on a linked system, this query would find all new objects in my main database where the same objects exist in my local database. This would work just perfectly, no problems.

Now, the local database is actually on a PDA running SQL Server Compact Edition. There is currently no support for creating a linked environment. I have the option of pulling the table off the local db and pushing it to the remote db and then running the above query from within the single db and then retrieving the list of new entries and pulling them down to the local db but that is a HUGE amount of bandwidth, even if I just used the single primary key column.

Would anyone maybe have a little advice for me on how I could possibly achieve the above result on SQL Server Compact please?


Thanks in advance

View 10 Replies View Related

SQL Server 2012 :: Querying A Supersession Two Column Table With Multiple Supersessions In Both Columns

Jan 29, 2014

I'm fairly new to SQL and am just setting up a Windows 8 app using an Azure SQL server. The issue I have is looking up a part number supersession and getting the latest number. One part number can have multiple supersessions (ie RTC5756 > STC8572 > STC3765 > STC9150 > STC9191 > SFP500160 ).The data I am supplied monthly has both the superseeded items and the supersession information in both columns and is not easy to decipher - for example:

Supersessions Table
----------------------

RTC5756 | STC9191
SFP500160 | STC9191
STC9191 | STC2951
STC3765 | STC9191
STC8572 | STC9191
STC9150 | STC9191

[code]...

The newest part number is kept in a separate table - called "source" - which in this instance is SFP500160. I need access to the latest part number but also to the part's previous numbers, due to the fact that some people may still be stocking them as an old part number and for them to search by. Is there an easy and efficient way of doing both a lookup for the supersessions and a join on the two tables to minimize the queries on the database?

View 9 Replies View Related

SQL Server Admin 2014 :: How To Read And Load Extended Events Into A Table For Querying

Jun 19, 2015

I am setting up extended events more or less just fine, however I am a bit confused as to how to read and load them into a table for querying. In particular the offset part - is there a way to load just a given dates worth in?

I've got the files configured to be 20MB before rolling over, the XE is running all the time.

So if i load in the full file now, say that covers 2.5 days worth, when I load it again tomorrow to get the updated data I'm also reloading today, which is a waste?

I presume I am going about this wrong, but lack an example that really goes into detail of practicalities of loading this data.

View 0 Replies View Related

Add Calculated Field In Order Table Based On Price Column In Product Table

Nov 18, 2014

I have 2 tables: Order(ID, Quantity) and Product(ID,Name, Price) and I want to add a calculated field in Order table based on the price column in the Product table. How do i do that?

this query returns the values i want in the table.

select a.quantity * b.price
from tblCustomerPurchases as a
join tblProduct as b
on a.ID=b.ID

View 17 Replies View Related

Table Order

Jun 8, 1999

I have a database with 200+ tables. How can I get table order (query) from the system tables so that I know which table I should insert data first.

Thanks,

View 2 Replies View Related

FROM Table Order?

Oct 23, 2007

I noticed that some queries against an mssql db require the tables in the FROM part of the statement to be in a particular order. Does anyone know why?

For example SELECT * FROM table1, table2, table3 WHERE <blah>

May throw an error (Unknown table table3 [I can't remember the exact verbiage of the error]), while simply rearranging the table order to:

SELECT * FROM table3, table1, table2 WHERE <blah> will work.

It seems like the error has something to do with how mssql handles the joins of the tables, but I can't seem to find any documentation about it.

View 2 Replies View Related

How To Order This Table.

Mar 19, 2007

I have a table

merchant contractbr date
a 2333 1/1/2005
a A34 3/12/2006
a R78 2/1/2005
..
b
b
b
c
c
c
.....

different merchant has different number of contracts.
I want to order the contracts for merchants according to the date.
the result table should look like:

merchant contractbr date order
a 2333 1/1/2005 1
a R78 2/1/2005 2
a A34 3/12/2006 3
..
b 1
b 2
b 3
c 1
c 2
c 3
c 4
c 5
.....

Thanks

View 5 Replies View Related

Regarding Table Order

Dec 16, 2005

Greetings,I have an application that need to get all the userdefined child tables first before their parents.I wrote a query, given in this newsgroup only, as belowSELECT o.nameFROM sysobjects oWHERE o.type='U'ORDER BY case WHEN exists ( SELECT *FROM sysforeignkeys fWHERE o.id = f.fkeyid )THEN 1ELSE 0end, o.namegoWhen i try to truncate the first table of the list, it still tells methat tha table is being referenced by foreign key in another table. Mymain job is to truncate all the user defined tables before loading datainto them.Is there something wrong in the query? Or if someone can tell me abetter approach.Any help will be appreciated.TIA

View 5 Replies View Related

ORIGINAL SIN IN SQL 2005!

Jul 23, 2005

from the December 2004 _Redmond Magazine_, "Finding Gold in Yukon":http://redmondmag.com/columns/artic...ditorialsID=838"Developers will also pick up the usual new-version enhancements to T-SQL,including error handling, recursive queries, a ROW_NUMBER function, a PIVOTrelational operator and more."ROW_NUMBER???? What possible purpose could that have???

View 5 Replies View Related

Decrypted Value Is Different From The Original One

Mar 29, 2007

Hi,

I got an issue with encrypt and decrypt. I encrypted a data, say 'ok', then I decrypt the encrypted data, but what I got is not 'ok'. but if I encrypt a column, then after decryption, those data asr same with the original data in that column.



In my case,



...

create table mytable ( column Password varbinay(128) )

...

create symmetric key with certificate

...

OPEN SYMMETRIC KEY Sym_Key_01

DECRYPTION BY CERTIFICATE Cert;



UPDATE mytable

SET Password = EncryptByKey(Key_GUID('Password_01'),'ok')



select CONVERT(nvarchar, DecryptByKey(Password)) AS "Decrypted Password" from mytable



here, I didn't get the value 'ok' but a another wierd word (like a chinese word).



does someone know the reason?



Thanks,



Jone

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved