Order By Two Columns

Oct 18, 2014

I have the following data:

Name Played Won
Player 1 15 14
Player 2 15 5
Player 3 14 13
Player 4 14 1

I want to be able to order by the number of games won by the highest played. So in the example above, I would get:

Name Played Won
Player 1 15 14
Player 3 14 13
Player 2 15 5
Player 4 14 1

How can I achieve that?

View 2 Replies


ADVERTISEMENT

Adding Columns In A Certain Order

Nov 20, 2000

I am familiar with the ALTER TABLE syntax that can be used to add columns to an existing table, but the columns are appended to the end of the table. Enterprise Manager allows you to insert a column in the desired place in the table, but I suspect that behind the scenes it creates a temp table with the new structure, drops the old version of the table and renames the temp table to the orginal table name (I could be wrong on this).

Is there a way to insert a new column in a specific place in a table using SQL rather than EM?

View 1 Replies View Related

Order Of Columns For Index

Jun 19, 2013

I want to make an index with the following columns, actually together they constitute the PK of the table so the index is created automatically.

The question is witch sort order is the best to have if I want to fetch all rows for one date and one resource? And why?

WHERE Resource = "Car 1" AND Date = "2013-03-03"

Resource, Date, Time

Or

Date, Time, Resource

Below is an example with tree resources, but in reality there can be a lot more and also years of dates.

Car 1, 2013-03-03, 10.00

Car 1, 2013-03-03, 11.00

Car 1, 2013-03-03, 12.00

Car 1, 2013-03-04, 10.00

[Code] ......

View 9 Replies View Related

Get Columns In An Index And Their Order

May 20, 2008

Is there a dynamic management view or system procedure which I can use to find out what columns are in an index, what columns are as an INCLUDE in the index and whether or not the column(s) are ascending or descending. This is excluding the utilities I already know about below:

sys.indexes
sys.index_columns
sp_helpindex
dm_db_index_physical_stats
dm_db_index_operational_stats
I only ask because it is a pain to look through the sys.indexes and sys.index_columns tables every time I want to know about what columns are in the index created. I also know that scripting the index would give me the information I need but there must be a better way.

Many Thanks

View 8 Replies View Related

Order Columns In PivotReport

Oct 8, 2007

Hi, I am using MS SQL Server 2005 9.00.1399.00. I am trying to make a PivotReport.
Everything is fine, but the columns are are ordered alphabetically, but i want that they are ordered in a specific way.
So i just created a new column ProcId in the Query, but how can i order the columns with the new column but display the other values.


i hope you understand what i mean!?

thanks

Philipp

View 3 Replies View Related

Order Of Columns Is Not Preserved

May 24, 2008

Hi

I am using a OLE_DB component source which connects to sqlserver and runs a query and the output of the query is written to a flat file destination.

Something like this

SELECT col1,

col2,
col3,
col4
FROM my_table

when i check in the mapping i see

col2 ------col2
col4-------col4
col3-------col3
...........

and thats what gets written to the flatfile destination.

i want SSIS to preserve the column order and write it as col1,col2,col3,col4
instead of what is happening now and it gets written as col2,col4,col3....

i am running sql2k5 and SSIS with sp2

is this a bug is there a solution for this or change the order of the columns for the flat file destination

regards
Hrishy

View 3 Replies View Related

ORDER BY, CASE, With Multiple Columns

Nov 7, 2003

I'm unable to specify multiple columns in my order by statement if i use a case statement.
Does anyone know why this is, or what syntax would make this work?

Thanks


SELECT ....
ORDER BY (CASE Lower(@SortExpression)
WHEN 'prodname' THEN prodname, prodprice
WHEN 'prodsize' THEN prodsize, prodname
WHEN 'prodprice' THEN prodprice, prodname
Else prodcompany, prodname
END)

View 5 Replies View Related

ORDER BY Columns In SELECT List?

Jul 20, 2005

According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?

View 1 Replies View Related

Cluster Index On 2 Columns Order

May 29, 2007


I have 2 columns in a table namely ColA and ColB.all DML operations are through views n every view has
Where clause i.e where ColA=€?€? with check option .
All most all my DML queries are using where clause on ColB
Where ColB=€?€?

Now my question is I have a clusted index on both ColA and ColB.in which order I have to create cluster index .
i.e ColA ASC,ColB ASC or ColB ASC,ColA ASC.

Is there any performance gain we can achieve with their order

View 1 Replies View Related

Dynamically Order Columns In Report

Mar 10, 2008



I have a report that displays data based on the last 12 months. Is there a way I can order the columns (header and data) based on the month it was run. eg. If I were to run the report in March, I want the columns to be ordered like this:

MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB

If run the report in April, I want the columns to be ordered like this:

APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC, JAN, FEB, MAR

So, the columns in the report are always ordered (12 months backward) based on the month it was run.

View 4 Replies View Related

SQL 2012 :: Using CASE In Order To Update Columns To Be Either A Or B

May 13, 2015

I am relatively new to complex queries and need creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. I execute the query and receive the error:

Incorrect syntax near the keyword 'from'.

select (select FirstName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,

[Code] .....

View 4 Replies View Related

Do Mapped Columns Need To Be In Exact Order Everytime?

Jan 23, 2008

In my SSIS package I have a text file source that I am mapping to a destination table. I have an error component that logs any row level errors and have noticed that it is not logging the correct field. I know this because I have a few different sources that submit the same files and have looked at the source of both. THE ONLY DIFFERENCE in the one that works versus the one that does not is that 2 of the 25+ columns are switched. I would not think this would matter because field A in the text file is mapped to field A in the database.

Does the order in which the fields come into the SSIS package matter?

Thanks

View 5 Replies View Related

How Do I Order Columns In My Custom Destination Component?

Nov 17, 2006

Here is the situation. I have created a package that takes 50 columns from a comma delimited flat file. I then validate and clean the data. Next I add two columns that were not in the original source file. These two columns need to be in the 5th and 9th column position when the file is then re-written to a text file. How do i get those two columns to write out in the desired order? Any ideas?



K. Lyles

View 1 Replies View Related

Order Of Columns In Flat File Destination

Oct 23, 2007

I need to create a number of flat files, all with the same layout and sourced from the the same table, but with different criteria.

The first set of (three) flat files file is created out of a simple Conditional Split transformation: If Source Table row number > 40,000 route to File 3; if row number > 20,000, route to file 2, otherwise route to file 1. This gives me 20,000 rows in files 1 & 2 and the remainder in file 3.

I also want to create a fourth flat file by joining the Source Table with a sample table and selecting only those rows where the Customer numbers match. I'm currently doing this in two stages: An Execute SQL Task performs the join and inserts the selected rows into a Destination table (identical layout to source table), and then a simple data flow moves the rows from the Destination table into the fourth flat file.

My problem is that the order of the columns in the first three flat files is different from the fourth file. I've tried creating the fourth flat file with a single data flow using a Merge Join transformation which didn't work because the tables aren't sorted in the correct sequence, and I couldn't get an OLE DB Command transformation to work either.

I'm not sure why the column order of the 4th file should be different seeing as how its contents are sourced from the same Source table, but is there a cunning way of setting this up so that the columns end up in the same order?

View 8 Replies View Related

Order By With Columns Of User Defined Table Type

Sep 28, 2014

I have a user defined table type with two columns: ID: int, Value: float.Also, I have a table with different columns.I have a stored procedure:

ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;

[code]....

I want to add "order by Value" to this stored procedure. Like below:

ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;

[code]....

But this way is not true, and I get error when i debug my application.I fill this user defined table type in c# with data of a DataTable.

View 4 Replies View Related

SQL 2005 Upgrade Insert Into Select * Changes Order Of Columns

Dec 13, 2007



Hi there,

This appears to be a change in behaviour between SQL 2000 & 2005. Can anyone confirm?

We have two tables with same schema but different column orders. In Sql 2000, the statement

Insert Into table1 select * from table2

appears to map the column names between the two tables. There is one column out of order, however Sql 2000 doesn't seem to care and correctly inserts the data.

In Sql 2005 the behaviour is to return the columns in the order of table2, rather than mapping column names. This results in incorrect values being added to the columns. (Column shift)

The Sql 2005 behaviour seems to be correct, and select * is bad practice anyway, however I would like to confirm why this was changed and whether there is a service pack/hotfix in 2000 that would have the same result.

We are running compatibility mode in Sql 2005 v9.0.3042.

Sql 2000 is 8.0.2187

thanks,
Andrew

View 1 Replies View Related

SQL Server 2008 :: Job Activity Monitor - Swapping Order Of Columns

May 28, 2015

Is there a way to permanently change the order of the columns in Job Activity Monitor?

I'd like to move Duration to the right of Step Name, but this only lasts so long as I have JAM open. Once I close it and re-open, JAM goes back to its default column order. Google gives me nothing but the temporary "drag and drop" method that I already know about.

View 2 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Order Of Rows And Columns Returned When Reading With Excel ODBC Driver

Jun 19, 2007

Hi,



I'm reading values from a named range within an Excel spreadsheet using the Excel ODBC driver. If I ask for all columns within a range, using e.g. select * from 'named range', does the driver ensure that the returned rowset has the same column and row ordering as in the spreadsheet? In other words, if a named range on a spreadsheet is the block of cells:



name age

richard 54

jemima 27



I want to make sure that my returned rowset is not going to be:



age name

jemima 27

richard 54



I know that proper databases do not guarantee the order of returned values (unless you specify it) but since Excel is a fixed view of data I was hoping that a returned rowset of values would retain their spreadsheet ordering.



Thanks,



aionaut



View 1 Replies View Related

Transact SQL :: Any Reason For Difference In Order Of Columns Between Index Of Constraint And Its Statistics Definition?

Sep 5, 2015

I am really puzzled by an apparent difference between table index key column order and its statistics order. I was under understanding that index statistics mirror index definition. However, in my db 2470 index ordinal definitions match statistics definition but 66 do not. I also can reproduce such discrepancy in 2008 R2, 2012 and 2014.

As per definition,

stats_column_id
int

1-based ordinal within set of stats columns

This script duplicates this for me.

BEGIN TRAN
GO
use tempdb
GO
CREATE TABLE [dbo].[ItemProperties](
[itmID] [int] NOT NULL,
[cpID] [smallint] NOT NULL,
[ipuID] [tinyint] NOT NULL,

[Code] ....

The result I get is this:

object_id       stats_name                                     
stats_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  cpID,  ipuID,

and

object_id      index_name                                     
index_column_list
1525580473 PK_ItemProperties_itmID_ipuID_cpID itmID,  ipuID,  cpID,

Also a query I used to discover this in my db is:

WITH stat AS
(
SELECT
s.object_id
,s.name as stats_name
,(
SELECT
c.name + ', ' as [data()]
FROM sys.stats_columns as sc

[Code] .....

View 6 Replies View Related

RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page

Dec 13, 2007

Hello:

I am running into an issue with RS2k PDF export.

Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .

User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.

We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.

Any help or suggestion on this issue would be appreciated

View 1 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

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)

How shall I achieve this?

View 4 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

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 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

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related

Find Order By Date Range Or Order Id

May 8, 2007

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
 but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez

View 4 Replies View Related

Default Sort Order When Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go


insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 14 Replies View Related

Recordset's Order And Database's Physical Order?

Jul 20, 2005

Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!

View 2 Replies View Related

Default Sort Order When The Order By Column Value Are All The Same

Apr 14, 2008

Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View 5 Replies View Related

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth



If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons


Any ideas?

View 4 Replies View Related







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