Hi, I've created a website usiing asp.net and all the data are stored in sql front. All the item are sorted in ascending order except one record. The correct order should be MP61, MP100, MP200, but this record is retrieved as MP100, MP200, MP61. If the coding is wrong, all the order displayed is not in ascending order. We have hundreds of items, but why it happens to this particular record? Can anyone help? Thanks in advance
View 3 RepliesHi ,
Hopw can i sort the data in the ascending order so that i can see the date of my log files imported at the top of my table .
Right now my log files get imported and appended in the tables but they get appended in the descending order at the bottom of the last data .
Is there any way to sort the data so that i can see it in the reverse way when i open my tables ?
Many Thanks .
Anita.
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
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?
I noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
Hi
I have a table with column name starts from col1 to col20. IS their anyway to insert the data and sort the table by col1 and col2 and SAVE IT.
How to sort table in sql2000 with ipaddress(format x.x.x.x) as column with nvarchar datatype in ascending order
without using stored procedure
Ex:
Table: netComputers(3 rows)
Column Name: ipAddress (string data type)
ipAddress
0.0.18.1
0.1.1.2
0.0.0.1
Sql query : if I use the query
Select ipAddress from netComputers order by cast( replace(ipaddress,'.','') as numeric(12)) asc
Gives result as :
ipAddress
0.0.0.1
0.1.1.2
0.0.18.1
Where as expected result should be:
ipAddress
0.0.0.1
0.0.18.1
0.1.1.2
I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.
So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.
Any suggestions would help.
Thanks
I have a problem with an Order By sort on a SubmissionDate column in my SQLSERVER DB.
I am inputing a timestamp in this format into the column above: 1/18/2005 11:03:19 AM
Problem is, once I sort this column in DESC order to return the results to a datalist dates with a time like this:
1/18/2005 1:03:19 AM
get placed out of place (lower on the return in DESC/higher on the return in ASC). I am assuming this is happening because it reads 1 as coming before 11 instead of after like it is with time. If this was in 24 hour format this wouldn't be a problem I guess because 1PM would be 13, so that is after 11.
Anyone know what I can do to get this sorted correctly?
Hi,
I have a situation where I need to sort the output of a cursor. But since the sort criteria are rather complex, I am NOT able to use the Order By clause directly with the cursor definition statement.
Hence, I need to have a solution where I will use a dummy (calculated) field within the CURSOR and I want the output of this cursor sorted by the dummy field that I calculated within the cursor itself.
Please let me know the different possibilities in this scenario.
Thank you in advance
Raj
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...
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
I am using sql statement like SELECT CREATEDBY,FIRSTNAME,BUSINESS,NOTES,NOTESDATE FROM BUSINESS ORDER BY NOTESDATE DESC, CREATEDBY ASC
But NotesDate is sorting descending order, but only sorting based on the date and month not on year
Please help me
I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,
Here is my code
@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ....
Hi there,
I got one prob and i need help to solve it.
I got this one field in a table which contain a lot of datas and i need to sort it out into as many different data as a field for each.
Example:
ASP_Tin No
----------------
ss24fg
ss98jk
tp98ij
yh88ij
yh67tr
tp34ed
fg98bv
fg56sl
..........
and i need to sort it out into like this
ss tp yh fg
------------- ------------- ---------------- ---------------
ss24fg tp98ij yh88ij fg98bv
ss98jk tp34ed yh67tr fg56sl
i need to do it in sql statement which is in stored procedure
hope somebody can help me
Thanx
I want to sort a stored procedure based on a variable passed to it... what is the easiest way to do this? Here's an example of what I want to do:
sp_select_thing 10, 'thing_name', 'asc'
It would run the query then somehow evaluate parameters 2 and 3 to be placed in the ORDER BY clause. I'm not sure if they should be quoted as strings or not, I don't have an idea how to pass a "reference to a variable" as a parameter to a stored procedure... or even if such a thing is possible
Thanks!
This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.
PLEASE HELP ME!!!
Pending deadline doom :(
Thanks,
kaskenasy@publishingconcepts.com
The file of about 30,000 records contains an address field where addresses are entered in the following form:
128 NW 8 ST
3444 SW 19 AVE
32 SE 122 TERR
12604 SW 148 PL
Then there are anomalies such as
123 E BLVD
(I can ignore the anomalies until the main challenge is overcome.)
The challenge is that I have been asked to sort the addresses in the following order:
quadrant (e.g., NW, SW),
street number (e.g., 122),
street name (e.g., TERR)
house number (e.g., 128)
It seemed like I should create a view that can be updated regularly, as records will be added/deleted ongoing.
How do I get these part into a four-column view? Or, is there another way to do this?
Esther
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too).
I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong).
So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet:
SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ?
well thanks for taking the time to read this, any help woulb be appreciated.
SSRS 2012 - VS2010...The report compares two years with a sort order on a value that has been engineered based on text switched to int. When sorting A-Z this is the result in the horizontal axis is: 5th, K, 1st, 2nd, 3rd, 4th, 5th..When sorting Z-A the result in the horizontal axis is:5th, 4th, 3rd, 2nd, 1st, PreK..Z-A is correct but A-Z sorting shows 5th as the start and end. The magnitude of the PreK location is correct but the label is wrong on the A-Z sort order. The sorting is implemented using the Category Group sorting option.
View 6 Replies View RelatedDoes ORDER BY work on character data type in SQL Server through ODBC?
I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results.
Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name
company_id = integer
company_name = 30 characters
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View 8 Replies View RelatedDear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View 2 Replies View RelatedHow can I "Order By" the second + third characters of a 7 char field ?
Sample data looks like:
LCA - L
LCB - L
LCF - M
LCE - M
LCE - A
LCA - A
LCB - A
If I order by the whole field I get:
LCA - A
LCA - L
LCB - A
LCB - L
LCE - A
LCE - M
LCF - L
LCF - M
What I want is:
LCA - L
LCB - L
LCF - L
LCA - A
LCB - A
LCE - A
LCE - M
LCF - M
I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M").
Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol).
All suggestions appreciated.
Thanks
Roger
Hi group,
I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar.
Question:
Is it possible to ORDER THEM as if they where of type DateTime?
EG
select [year], [month]
from tbl_WeightedAverageGenerated
where [Year] = 2006
ORDER BY [Month]
Returns:
2006, 10
2006, 11
2006, 12
2006, 5
2006, 6
etc...
I need it to return:
2006 5
2006 6
2006 7
2006 8
2006 9
2006 10
2006 11
2006 12
Is this possible....and how??
TIA
Regards,
SDerix
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
Hi,
I have small question regarding ORDER BY clause.
I wanted some value of the fields should come first before other values….Something like:
+----------+----------------------------------------+
| code | name |
+----------+----------------------------------------+
| UK | United Kingdom |
| US | United States |
| AF | Afghanistan |
| AL | Albania |
| DZ | Algeria |
| AS | American Samoa |
I want to display country UK and US code first and then rest of the countries using SQL statement. I know in MySQL, I can use ORDER BY code IN (...)
--SQL
SELECT * FROM countries ORDER by code IN ('UK', 'US') desc
But not sure how can I do same thing in SQL Server. Is there any equivalent of ORDER BY [field] IN (…) clause in SQL Server???
Though I can create a new field something like sequence and use it to display these countries but I can not do that…
Please advice.
Thanks,
Firoz Ansari
Hi,
How can I rearrange the fields in a table in SQL 7? If it is possible, will the existing data be lost?
Thank you for any help.
Newbie question. Can someone show me an SQL statement that sorts the results of a query by a field in a different table?
CREATE TABLE `Table1`
(
`table1_id` INTEGER AUTO_INCREMENT ,
`table1_name` VARCHAR(255),
PRIMARY KEY (`table1_id`)
)
CREATE TABLE `Table2`
(
`table2_id` INTEGER AUTO_INCREMENT ,
`table2_name` VARCHAR(255),
`table2_table1` INTEGER,
PRIMARY KEY (`table2_id`)
)
ALTER TABLE `Table2` ADD FOREIGN KEY (`table2_table1`) REFERENCES `Table1`(`table1_id`);
What I'd like is something like:
SELECT * FROM Table2 ORDER BY "Table1(table2_table1).tabel1_name",table2_name
It's the section in double quotes that I can't figure out how to compose.
As an example, if Table 1 has
1, A
2, B
3, C
and Table 2 has
1, a, 2
2, b, 1
3, c, 2
then I'd like the sort to return
2, b, 1
1, a, 2
3, c, 2
TIA,
Stephen
Hi AllI am finding unexpected results when inserted into a newly createdtable that has a field of datatype int identity (1,1).Basically the order I sort on when inserting into the table is notreflected in the order of the values from the identity field.Have I been wrong in assuming that it should reflect the order from thesort?The code is ...create table tmp (A varchar(50), L float, C int identity(1,1))insert into tmp (A, L) select Aa, Ll from tmp1 order by Aa, Lland I don't understand why the values in tmp.C aren't in the ordersuggested by the sort.Any comments most appreciatedBevan
View 13 Replies View RelatedBelow is the statement i am working with and i am trying to figure outhow to have the count in ascending order...is this possible? Right nowit groups by FP in alphabetical order but i dont want that i want thewhatever the FP1 count is to be ascending but i am not sure how to goabout doing this...so any help or ideas?SELECT FP1 AS FP, COUNT(FP1) AS Expr1FROM dbo.FP1WHERE (FP1 IS NOT NULL) AND (LTRIM(RTRIM(FP1)) <> '')GROUP BY FP1
View 3 Replies View RelatedHi,
have uncovered a curious problem with sort ascending in tables.
In a table I'm grouping on CONTRACT_ID field. The grouping works fine but the sort acending on CONTRACT_ID doesn't.
Here are the actual contract id's I have to sort:
0202018B
0202019C
0202020G
0202021H
0202022J
0202023K
0202800B
The above list is how the Crystal Report I'm converting does this sort - i.e. the normal, common sense, intuitive answer.
Here's how SSRS does the sort:
0202022J
0202021H
0202019C
0202800B
0202018B
0202023K
0202020G
i.e. a completely non-intuitive, non common-sense answer!
I can probably solve this by stripping the last character and converting to int via an expression, but why does one get a completely non-sensical answer with the standard values? What is the logic by which Microsoft have implemented sorting. It seems very odd to me. Can anyone shed any light on this?
thanking you in advance,
cheers,
rob
Hi Guys,
Does anyone knows what is the evaluation order for expressions attached to "Value" property and "BackgroundColor" property for a field.
My problem is if a field is being changed in its value property to a particular value, will this changed value be recognised immediately by the Backgroundcolor property during its rule evaluation, it does not seem to be the case.