How To Order A Column

Nov 30, 2004

To order a recordset I can simply do
"Select * From Table Order by ColumnName;"

But my need is different.

In a column I have the following content:
"C"
"P"
"F"
"A"

But "F" and "A" may exist more than once.

I must order the recordset always in the order
"C"
"P"
"F"
"A"

and not "A", "C", "F", "P".

Can anyone pls help me?

Thanks in advance.

View 5 Replies


ADVERTISEMENT

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

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 2 Column ?

Dec 14, 2003

i have i table tab1 and 2 columns date1<b> and <b>date2

i want order this cloumns but i have one thing

* if date1 < date2 then order by date2

* if date2 < date1 then order by date1

how i do it .?

thanx

View 3 Replies View Related

Column Order

Jun 6, 2001

Does anyone know how to change column order of a table with data.

View 6 Replies View Related

Does Column Order Really Matter?

Sep 11, 2006

Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with?  I'm kind of confused.  

View 2 Replies View Related

Order By Column Alias

Feb 12, 2007

I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS
2 (
3 SELECT
4 Id,
5 Name,
6 Created,
7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers,
8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
9 FROM Projects p
10 )
11 SELECT *
12 FROM ProjectList
13 WHERE RowNumber BETWEEN 50 AND 60;

This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error:
Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'.
I read this in the documentation:
When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions.
So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this

View 4 Replies View Related

Variable ORDER BY Column

Jan 20, 2000

I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.

View 1 Replies View Related

ORDER BY Any Column Problem

Jan 22, 2001

I am using SQL2000.

I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.

Is there a way to do this ?

I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope.
I've tried SETting a variable to the above @OrderByThisColumn ...nope.

Any ideas/clues would be a great help

The code...........

CREATE PROCEDURE usp_ShareHolders_Main_sel

@CompanyCode varchar(4),
@OrderByThisColumn varchar(30)

AS

SELECT
H.Fund_Man as Holders,
H.Shares as SharesHeld,
H.Share_Pric * H.Shares as Value,
H.Pcent as SharesOutstanding,
H.Shares - H.Shares as ShareChange,
C.Reg_Date as ReportDate,
'Register' as Source,
((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent,
H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight,
(H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit

FROM
Citywatch_Company C
Inner Join
Citywatch_Holders H
On
C.Epic = H.Epic
Inner Join
Citywatch_Sector S
On
H.Sector = S.Sec_Code
WHERE
C.Epic = @CompanyCode

ORDER BY <one of the select columns above>


Help me please someone
Paul

View 3 Replies View Related

ORDER BY - Join Column

Apr 6, 2007

Hi, I am writing a small search engine.
There are two tables. The first one holds the search engine main index, the second one is link table.
I have the following query that retrieves results. I would like to sort the results by:
dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query?
I'm using MSSQL 2005. Thanks.
PS. Function OCCURS2 returns number of occurrences of one string in other.

Code:

select
id as Id,
uri as ElementUri,
size as Size,
modified_date as ModifiedDate,
title as Title,
text as Text,
dbo.OCCURS2(LOWER(:query),Title) as TitleOcc,
dbo.OCCURS2(LOWER(:query),Text) as BodyOcc
FROM se_index
WHERE (title LIKE :query) OR
(text LIKE :query) OR
(id IN
(SELECT se_links.target_index_id
FROM se_links INNER JOIN
se_index AS se_index_1 ON
se_links.target_index_id = se_index_1.id AND
se_links.anchor LIKE :query))

View 1 Replies View Related

How To Order In Month Column

Nov 15, 2007

hi,

table ab contain two column
no dispaymonth
1 April
2 jan
3 Mar

when i using order by in Month column it will return Alpha/- order(April,Mar,jun like that) but i need Jan,Feb,March order

need that Query

Advance thanks

View 3 Replies View Related

Using The Same Column In Bother SUM And ORDER BY? How?

Jun 21, 2007

Hello,Using SQL 2005. Columns:ID, int (PK, auto-increment by 1)WorkHours, intName, varchar(100)I can't seem to get the following query to work. I want to return allNames and the sum of ALL work hours, in each row and order by eachINDIVIDUAL work hour:SELECT Name, SUM(WorkHours) as hFROM EmployersORDER BY WorkHours DESCIt seems that putting WorkHours in but the aggregate function and theORDER BY clause creates a problem.Thank you for your help!

View 2 Replies View Related

How To Re-order The ID Column Of A Table?

Oct 5, 2007

I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?

Thanks in advance.

View 6 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007



I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC

View 1 Replies View Related

A Column Has Been Specified More Than Once In The Order By List.

Apr 18, 2007

I have a function dbo.FIELD that extracts a part of the value from a field. I want to get two pieces and order by them.



Select failed: Warning: 169(15): A column has been specified more than once in the order by list. Columns in the order by list must be unique.



How can I do this?



SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD(WORK_ID, '*', 1) ASC





These work:

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, WORK_ID ASC

SELECT WORK_ID FROM WORK ORDER BY dbo.FIELD(WORK_ID, '*', 2) ASC, dbo.FIELD('WORK_ID', '*', 1) ASC



View 1 Replies View Related

Using Group By And Order By With The Same Column

Apr 9, 2008

Hi All,

Please let me know if both query fetch the same result

Select Col1, Col2, Col3, Col4 from Table1 Group By Col1, Col2, Col3, Col4
Order By Col1, Col2



Select Col1, Col2, Col3, Col4 from Table1
Order By Col1, Col2

Thanks,
Muthu

View 7 Replies View Related

Can You Use Column Order Value In Select Statement

Nov 6, 2005

Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View 2 Replies View Related

Composite Index And Column Order

Jun 9, 2006

Hi,I created a composite index (lastname, firstname). I know the followingqueries will use this index:WHERE lastname = ...WHERE lastname = ... AND firstname = ...Also this won't use the index:WHERE firstname = ...But how about: WHERE firstname = .. AND lastname = ...And why?Thanks a lot,Baihao--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View 13 Replies View Related

Changing Column Order In A Table

Jul 20, 2005

This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!

View 2 Replies View Related

Alter Table And Column Order

Jul 20, 2005

Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table.TIALaurence Breeze

View 2 Replies View Related

How To Change Column Order In Tables?

Apr 14, 2008

I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?

View 1 Replies View Related

Transact SQL :: Order By A Column From Another Table

Sep 2, 2015

I have two tables, one is called (questions), the second one (answers).

questions columns are (ID,questionTitle)

answers columns are (ID,questionID,answer, answerDate)

I use this query to load data: "SELECT q.questionTitle,COUNT (a.ID),a.answerDate FROM questions q LEFT JOIN answers a ON q.ID=a.questionID" the query is easy, but my problem which I can't solve is how can I fetch the data ordered by the column answerDate, I mean I want the first record to be the one which has the most recent answer and so on.

View 12 Replies View Related

Column-order An Access Speed?

Jun 15, 2006

Is there any truth to this: the placement of fields in a table relates to field access speed. So, frequently accessed fields should be placed in the beginning of the table while fields infrequently used can be placed toward the end.



TIA,

Barkingdog

View 4 Replies View Related

Column Sorting In Ascending Order

Nov 27, 2007



Hi

I have Constructed a table

Table Name : MasterEntry

Column Name:
MasterEntryNumber
ServiceName
ServiceDepartment
EmployeeName

MasterEntryNumber is GenerationNumber where ever any entry is happen in table

if put a queries [select * From masterentry Order by MasterentryNumber] is give output in ascending order

e.g MasterEntryNumber has
1
2
3 it give correct ordering

but if i exceed more 10 if i put same queries

Output of queries is
1
10
11
2
3 this is the out of the queries if it exceed more 10 rows

i want queries should be look like this
1
2
3
...... 10

did any have experience on this issue please let me know where i can fix

kinds regards


View 8 Replies View Related

Partitioned Index Column Order

Aug 22, 2007

We are using partitioned unique indexes on partitioned tables. When the Unique Index is built, should the column the index is partitioned by be the top (leftmost) column in the index? While this violates cardinality, it makes sense (at least to me) that the first thing the query execution would do is figure out which partition(s) contain the result set, then filter from there.

What do you guys think? Is there any documentation on optimizing partitioned indexes?

View 1 Replies View Related

Display Database Column In Alphabetical Order

Mar 12, 2007

Hello
I know how I can display a list of names in alphebetical order on my website:
Select L as [Last Name]
From  Name_CatEWhere Education = 'yes'Order ByLName ASC
However, to make things a little more orginised I would like to view my database table column in alphabetical order also, but ithie code does not work within my database.
What do I need to change in the following code, to view my database table column in a-z order?
 SELECT LName FROM Name_CatEORDER BY LName ASC
Thanks
Lynn

View 1 Replies View Related

Updating Database Column After Order Has Been Made

Jun 7, 2004

i am building a shopping cart. I want to update the UNIT_IN_STOCK column in database after order have been submitted. i want to subtract the quanity value from the order made from the UNIT_IN_STOCK column in database. how would the sql statement be like?? i tried this but it didnt work. any suggestions??

CREATE PROCEDURE update_Products_By_name
(

@ProductName varchar,
@UnitInStock int

)
AS

UPDATE Products
SET UnitInStock=(UnitInStock-@UnitInStock)
WHERE ProductName = @ProductName
GO

View 1 Replies View Related

Does Column Order Matter When Creating A Table?

Mar 16, 2001

Does column order matter when creating a table? For example, Should NOT NULL columns always come before NULL columns? Should most frequently used columns always be near the top? What about text, ntext and image data types? Should they always appear near the end of the column order?

View 1 Replies View Related

Sorting A Column In Descending Order Which Contains Two Years

May 21, 2014

I am trying to sort this simplied table:

ID - Time
1 2000-2001
2 2002-
3 2001-2003
4 1999
5 2005-2006

I want this as a result:

1999
2000-2001
2001-2003
2005-2006
2002-

Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...

Now, simply doing a
SELECT * FROM [table] ORDER BY TIME;

Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.

So, of course, it fails on all entries with a leading "-" ...

Right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...

Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it...

View 5 Replies View Related

T-SQL (SS2K8) :: Order By On Date Descending In A Column

Jun 18, 2014

Below SQL gives the results of 2 columns and I need the DepartmentandDate column to be ORDER BY on date desc for that CID. Expected result is in the screenshot(attachment)...

IF OBJECT_ID('Tempdb..#tTable') IS NOT NULL
DROP TABLE #tTable
CREATE TABLE #tTable(CID INT, CDate DATETIME, Dept VARCHAR(25))
INSERT INTO #tTable(CID, CDate, Dept)
VALUES
(111, '2014-01-14 00:00:00.000','B is alphabet'),

[Code] ....

View 6 Replies View Related

Column Aliases In Case Statement In Order By

Jan 18, 2007

Hi All,

I have this query :

select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end

when i execute the above query it gives me the following error message.

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.

Thanks in advance.

Thanvi.

View 5 Replies View Related

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Sorting Matrix Column In Random Order

Aug 14, 2007



Hi,

I have a matrix report...the column results
are as follows

Con Std , Con Access, SF Std, SF Acc, Broadband, Pay TV

how would i make the columns appear in the above order when displaying as it is default alphetically sorted...I have tried putting numbers at the front which work till I get to the number 10 which alphetically sorted is next to 1 not 9?
is there a better way off sorting matrix column which have no specific criteria to sort from?

thanks

View 6 Replies View Related







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