Select Sort
Mar 18, 2004I am trying to select a record from a table where it has the smallest priority
how would you go about doing this
is there a cool sort command or is there a select command syntax that can do this
thanks
ADVERTISEMENT
Is There Any Way To 'sort' The SELECT Statement?
Oct 4, 2007hi,
i have a stored procedure SELECT PictureID, Left(Name, 16) + '...' AS ShortName
FROM Pictures
some pictures has their name lower that 16 characters, and when i show on the page is something like "Sunrise..."
how can i 'sort' the Select so to return the ShortName if the name is greater than 16 characters or return Name if is lower
i hope you understand what i mean...
thanks
SELECT, SORT, And SUM Query
Oct 14, 2005Hello everyone,
I Have a table with columns:
- CustomerID
- DateTransactionCompleted
- AmountPaid
Illustration data as follow
GO
INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-12-2004','18000.00')
INSERT INTO Cus_Tab_Dev_2 VALUES(30,'12-15-2004','2000.00')
INSERT INTO CUS_Tab_Dev_2 VALUES(30,'1-16-2005','15000.00')
INSERT INTO CUS_TAB_DEV_2 VALUES(42,'2-2-2005','12000.00')
GO
What I want is a report that tells me the Total Sales per month Per Customer in the following manner:
Customer ID Month/Year Total Per Month
----------- ----------- ----------------
30 12-2004 20000.00
30 1-2005 15000.00
42 2-2005 12000.00
This is a homework, and I have been trying to get it to work since this morning. Any help is appreciated.
PS: If you do not feel you want/should help. Please don't call me names! I don't ask people to help me with my homework usually. But, I am running out of time on the assignment.
Sort And Select, Which Executes First?
Sep 15, 2006I use SQL Server 2005.
I want to split the data in a table into two parts, say 30%-70%.
Here are my query statements:
-- 30%
SELECT top 30 percent * FROM DataTable ORDER BY Col1
--70%
SELECT top 70 percent * FROM DataTable ORDER BY Col1 DESC
Somebody says the result is not guaranteed to be correct since in SQL Server 2005, the query optimizer may choose different strategy to execute the query statement.
I think select should execute after order by, which has nothing to do with query optimization. Am I correct?
SQL Server 2008 :: Multiple Languages In Same Column - How To Sort / Select
Jul 29, 2011We have a database where the nvarchar columns currently holding English only data. It keeps the training information. Currently the default collation is Latin Case insensitive accent insensitive.
Now we are planning to allow multiple language support. When we go for it, we will upload the data from different languages. Based on user preferences, he/she should be able to query the data (One language at a time).
I am worried on how the existing queries will work if I load all language data in the same database.
For example, some characters in English are used in Norwegian too. But they have different sort order. ALso LIKE conditions too may fail.
So far I thought of few solutions: Add the Collation information along with select , order by clauses. It means we need to add more procedures (one set per collation) The other option is to create new database for each language. Each will have its own collation. The dowside is we need more databases which may lead to more servers and more maintenance work.
Dynamic Sort Column And Sort Order Not Working
Aug 7, 2007I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
Default Sort Order - Open Table - Select Without Order By
Mar 27, 2008Hi!
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
Cannot Sort A Row
Oct 26, 2006i encounter this error..
Cannot sort a row of size 8107, which is greater than the allowable maximum
of 8094.?
why this error occur? can someone explain? how to avoid this? thanks!!
To Sort Or Not To Sort
Dec 10, 2007We are using a modeling technique called Anchor Modeling in our data warehouses. You can read more about the technique itself at our homepage http://www.intellibis.se, where we have published a fact sheet and a recently held presentation (TDWI European conference). One of the features with this technique is its simple way to historize data. This is done by having a fromDate column which together with the surrogate key will yield a unique combination. On the tables that has this kind of historization we add a primary key, which in turn will create a clustered index, with the following specification (surrogateKey asc, fromDate desc). This will physically order data on the storage media according to the specificed columns and ordering. Now I move on to create a "latest view" of this table which does a subselect to find the latest version for every surrogateKey using max(fromDate). Should not the optimizer now figure out that data is ordered so that the latest version always comes first for every surrogateKey, hence any sorting would be unneccessary? If I look at the actual execution plan after running a query that uses the view there is a sort in the plan, but the cost is always 0%. Does this mean that it did not sort the data, or that it did call a sorting routine, but it actually took very little time to do the sorting? If so, is there a reason that is has to do the sorting or could it have been left out by an even smarter optimizer?
I would also like to applaud the people behind the optimizer, since it will figure out which tables are in fact necessary to query and eliminate others, even if I have left joined them into the view I am using. This speeds up performance and makes anchor modeling feasible. Unfortunately optimizers from other vendors seem to have trouble doing this...
Regards,
Lars
SQL Sort Help
Mar 11, 2008I've been racking my brain all day and I finally decided to ask for help. I've got two tables with rows from the first that need to be sorted by the second. The problem is that the rows don't always exist in the second table. I've tried various forms of INNER, LEFT, RIGHT, OUTER, LEFT OUTER, CROSS, etc., etc., etc. and nothing (oh yeah UNION too). Every time I get close, I lose the records that don't have matches.
Something close-
SELECT A.IDDoc, B.First
FROM A
LEFT JOIN B
ON A.IDDoc = B.IDDoc
WHERE B.Dept = 'A'
ORDER BY B.First
Example Data
Table A
IDDoc Document
---------------------------
1 1467.doc
2 8722.doc
3 A47F.doc
4 88DQ.doc
5 ABCD.doc
Table B
IDDoc Dept First
----------------------------
1 A John
2 A Bob
3 A Ralph
4 A Diane
Results I Want
IDDoc First
-------------------
5 NULL
2 Bob
4 Diane
1 John
3 Ralph
Any help is appreciated. If I've posted in the wrong forum, please feel free to direct me to a better one.
Thanks in advance!
Jim
How To Sort The Date ?
Oct 12, 2006SELECT
LEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' +
RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4) as Date,SUM(CASE a.status_id WHEN 1000 THEN b.act_point ELSE 0 END) as Programming,SUM(CASE a.status_id WHEN 1016 THEN b.act_point ELSE 0 END) as Design,SUM(CASE a.status_id WHEN 1752 THEN b.act_point ELSE 0 END) as Upload,SUM(CASE a.status_id WHEN 1032 THEN b.act_point ELSE 0 END) as Testing,SUM(CASE a.status_id WHEN 1128 THEN b.act_point ELSE 0 END) as Meeting,SUM(CASE a.status_id WHEN 1172 THEN b.act_point ELSE 0 END) as OthersFrom
task_table a,act_table b where a.status_id=b.act_id and
a.user_id=(select user_id from user_table where user_name='Raghu') and
a.task_date like '%/%/2006' GROUP BYLEFT(CONVERT(CHAR(11),convert(datetime,task_date),109),3) + ' ' + RIGHT(CONVERT(CHAR(11),convert(datetime,task_date),109),4)Output :Aug 2006 294 0 0 80 0 0 Jan 2006 14 0 0 0 0 0 Oct 2006 336 0 0 0 0 0 Sep 2006 3262 20 24 8 16 0 How to sort the date in ascending Order ?Jan 2006Aug 2006Sep 2006Oct 2006
Sort Of Intersect
Feb 27, 2008I have: 4 tables and 1 table variable.
CCenters (ID, Name)
Campaigns (ID, Name)
Rel (ID, CCenterID, CampaignID) - [many to many]
and @SCampaigns (ID, CampaignID) - represents the selected campaigns by the user
performing the commands below I would get the centers associated with the campaigns selected.SELECT CCenterID
FROM Rel
INNER JOIN @Campaigns ON @SCampaigns.CampaignID = Rel.CampaignID
But what I really want are the common centers to the selected campaigns.
Thanks
Need Urgent Help To Sort This Out!!
Aug 31, 2005I've made this example and it loads a picture into a database. (MsSql )Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"Is it supposed to be like this or is it supposed to be reemoved after .net is finished??Code snip_______________________________________________________
Dim conn As New SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")
Dim cmd As New SqlCommand("Select * from tab_bild", cnn)
Try
conn.Open()
Dim myDatareader As SqlDataReader
myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
Do While (myDatareader.Read())
Response.ContentType = myDatareader.Item("PersonImageType")
Response.BinaryWrite(myDatareader.Item("PersonImage"))
Loop
conn.Close()
Response.Write("Picture info succesfully retrieved")
Catch SQLexc As SqlException
Response.Write("Read failed, Reason: " & SQLexc.ToString())
End Try
End Sub________________________________________________________________Please can someone explain this for me or sort this out for me.All help is welcome even if its only points me too a direction.RegardsTombola
Sort By Occurence
Mar 29, 2001When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
Sort By Occurence
Mar 29, 2001When sorting records in a table how do you sort by the occurence of a field.
So, if the table contained: 1,1,2,2,2,3,3 how would you get it to sort using the desc syntax to give 2,2,2,3,3,1,1
Sort Order Id. ?
Jul 20, 2000Hi,
I am trying to restore .DAT file from dump. Its giving me error ..saying that the sort order id used for dumping was 42 not the default value 52.
How can i change the sort order to 42.
I am using sql server 6.5
Thanks
Srinivas
Sort Order
Jan 13, 2000We have a vendor who insists that sql server 7 be set to a binary sort order. Is there any real advantage to this as opposed to a dictionary sort?
View 2 Replies View RelatedSort / Group?
Sep 21, 2000I have a table that most of the data has the same value, but there are only a few that do not match that value. I want to populate a listbox with all values from the table, but I'd like to have the majority listed first, followed by the others (the few that don't matach). What's the best way to approach this with SQL?
Thanks.
SORT ORDER
Sep 20, 2000HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?
HELP
Sort Orders
Oct 5, 1999Is there a query you can run against a 7.0 server to return the chracter set and sort order.
View 1 Replies View RelatedSort Order ID's
Oct 18, 1999I'm trying to setup a duplicate of an old SQL Server 4.2 server to put in place while we upgrade the server, but I can't get the sort-order right. I know the existing server uses sort order id 40, but I can't find which sort-order that corresponds to during the install process. If anyone can give me a system table that lists all the sort orders names and id's, or can tell me what the text name for sort order 40 is, I would be very grateful.
Thanks,
Rob.
How To Sort NULLs?
Jul 13, 2004Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
BCP And Sort Order
Jul 17, 1998I need to copy the structure and data of an existing SQL 6.5 server to one with a different sort order. Normally, I would use the transfer tool to accomplish this, but the servers are on different networks. My question is, is BCP the answer? In other words, will the data copied via BCP from the sending server be able to be copied on the recieiving server. Also, is there a way to automatically generate the BCP statements for all tables? What I would really like is to be able to get at the scripts and data files created by the transfer tool.
View 1 Replies View RelatedSort Order
Jul 9, 2001How can I set the sort order to 42, nocase when I install sql server 6.5
does Setup gives you some option to check to set sort order ?
Sort Order
Mar 23, 2001For SQL 2000,
Can I assign different sort order on the database level?
thanks!
Sort Order Id 42 ?
Jul 24, 2000Hi,
Can any one pls tell me what this sort order id 42 corresponds to and how its different from 52 ?
What options i need to check during installation for sort oreder id. ?
Thanks
Srinivas
How To Sort NULLS
Jul 13, 2004Hi All,
I have a table with 3 columns. Product, Location and Value. The data looks like this:
NULL NULL 100
Atlanta NULL 50
Atlanta Cookie1 30
Atlanta Cookie2 20
Dallas NULL 120
Dallas Cookie1 80
Dallas Cookie2 40
This table gets filled with a Groupby with Rollup option. The NULLS show subtotals/total. Is there a way to build a query that returns the results with NULLs at the bottom of each section like:
Atlanta Cookie1 30
Atlanta Cookie2 20
Atlanta NULL 50
Dallas Cookie1 80
Dallas Cookie2 40
Dallas NULL 120
NULL NULL 100
Thanks,
Shab
Sort MDX Query
Feb 28, 2005Trying to get data from result of MDX query in dec order:
iLoopFrom = cst.Axes(1).Positions.Count - 1
iLoopTo = 0
iLoopStep = -1
im using these lines to read the data from bottom to top but what exactly does Position.Count do? and is the -1 there because the result has headers??
Thanks
Sort Table
May 10, 2006Hello,
Is there a way to re sort data in a table? based on ID.
The
SORT TABLE tlb_Name ON ID Asc
is not working?
Thank you very much.
Different Sort Orders ??? Please Help
Feb 12, 2008Hi There i have two windows 2000 servers which are both running SQL and i would like to restore a backup from one server to the other. Which in my opinion should be an easy task but when i go into the restore option and point it at the file i would like to restore i get the follwoing error
"The database you are attempting to restore was backed up under a different sort order ID (52) than the one you are currently using on this server (50) and at least one of them is a non binary sort order. Backup or restore operation operation terminating abnormally."
The server that i am trying to restore to already has databases on this so i cannot just reinstall SQL and change the sort order not that id know how to do that but this is what i have read.
Is htere anyway that i can put insome script for the database to fix this ???
Im using Enterprise manager with SQL server 7
Thanks in advance
Sort IP - Find Max And Min
Nov 28, 2013I am struggling with
MIN(CAST(PARSENAME (ipaddress,1) as decimal)) + '&' + MAX(CAST(PARSENAME(ipaddress,1) as decimal)) as LowestAndHigestIP
I get error message "Error converting data type varchar to numeric." i guess that '&' is the issue. I want to be able to sort high and low IP like number not string.
ipaddress is varchar
Sort Order
Apr 22, 2008How do I create a sort order column in a view? I could use Row_Number() in 2005 but unfortunately I need to create a sort order column in a 2000 view and the View is not letting me to use an ORDER BY.
Any inputs?
Prakash.P
The secret to creativity is knowing how to hide your sources!
Sort Record By Sno
Mar 28, 2006i have to sort the records in the table. When i write
select sno from employers order by sno
Result ::
1
10
11
12
2
20
21
22
i want the result as
1
2
10
11
12
21
22
can you Kindly tell me how to get the records listed