Sort By Occurence

Mar 29, 2001

When 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

View 2 Replies


ADVERTISEMENT

Sort By Occurence

Mar 29, 2001

When 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

View 1 Replies View Related

String Occurence

Dec 2, 2003

Hi all,

Is there a function in SQL that will return the number of occurence of 1 string to the other.

e.g.

string1 = "1"
string2 = "2131145"

function should return 3

How do i obtain this in SQL?

Thanks in advance

View 9 Replies View Related

First Occurence Of The Record

Jan 6, 2004

Can any one let me know as how can I query the first occurence of a record. I need to select all distinct Order Types from a table. If there is more than one record with the same Order Type, then I need to get the first record.

It would be a great help to me.

Thanks,
John

View 4 Replies View Related

Select Row With Only ONE Occurence

Mar 1, 2006

Hi all,How can I get a row that has only one occurence in a table? Notthrough 'distinct' because this gets a single row that might havemultiple occurences, I want to get only rows that have only oneoccurence in the table.I tried the group by and within the having clause I placed thecount(myfield) = 1, did not work...select f1, f2, count(f2)from table1inner join table2 on table1.id = table2.fidwhere .....group by f1, f2having count(f2) = 1gives me result like this: (basically in this situation, I dont want toreturn any results)f1 f2 count(f1)--- --- -----------1 a 12 a 1any ideas sql gurus?thanks

View 3 Replies View Related

Counting The Occurence Of A String ...

Aug 27, 2006

Hi ...I have a weblog database where I want to count the occurences of atable of string values that appear in all the urls viewed.My tblWebLog as a field that contains the url ...tblWebLog.[cs-uri-stem]I have another table ... tblStrings ... that has a field [strSearch]for a string value and an integer field [intViewCount] to count theoccurence of the string in tblWebLog.[cs-uri-stem]I've been trying ...Update tblStringsSet [intViewCount] = (Select Count(*) From tblWebLog Where[cs-uri-stem] Like '%_' + tblStrings.[strSearch] + '.htm%').... but it doesn't fly and I'm stumped. Any thoughts?Cheers.

View 2 Replies View Related

How To Get The Last Occurence Of Rows Containing Disticnt Value In One Column

Jul 23, 2005

I have a table that tracks GPS records broadly speaking as followstsDATETIMEusernameVARCHAR(16)...GPS data...I want to select the most recent GPS data for each distinct user.That is, the table will have may records for a given username, but Ionly want the most recent for each one.For a single user I know I can doSELECT TOP 1 * from <table> order by ts descBut I want a set of results that effectively does this forall users, and I can't work out the necessary query/subquery Ishould be using.I'm sure I'm missing something fairly obvious, so usual newbiedisclaimers apply.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/

View 4 Replies View Related

Strange Occurence In SP4 On Sql Server 2000

Aug 7, 2007

We have a strange occurrence in one of our dev Sql server. Our platform is Sql Server 2000 on SP4. Build Number is 8.00.2039

TableA

Sid int, --Non-Clustered Index

Aid int,

Uid int,

€¦..

TableB

Iid int,

Sid int, ---No foreign key constraint to TableA.Sid but logically refers to that column. --Non-Clustered Index

€¦..

Now, we are working on schema changes, now TableB looks like

TableB

Iid int,

Uid int, ---Logically refers to TableA.Uid

€¦..
Sid in TableB is dropped and a new column Uid is added.


And there is no Sid in TableB.

We have an existing SP that refers to TableB.Sid like

Create proc sp_ab

As

Select Uid from TableA where Sid = (Select Sid from TableB where Iid = @Iid)

After making the schema changes listed above, if I try to compile this SP without making any changes to the procedure, it should fail. Surprisingly it passes compilation and also retrieves data using hash joins and doing table scans on TableA avoiding index seeks. This SP used to take less than second, now takes almost 90 seconds to retrieve data. I couldn't replicate this problem with another set of objects.

If I make changes to the SP sp_ab like

Create proc sp_ab

As

Select Uid from TableA where Uid = (Select Uid from TableB where Iid = @Iid), it runs less than a second as it should.

Whats surprising is, if I do 'Select Sid from TableB where Iid = 1 ' in Query analyzer, it throws error referring to invalid column Sid in TableB but couldn't throw this error when the sql is wrapped in a SP. Everything else in the DB and in the db server seems to be ok except this.

If I query Syscolumns table, I couldn't see any references to Sid in TableB after the schema chnages and not sure why the code in SP is not caught during compilation. Any clues.

View 1 Replies View Related

Check The First Occurence Of A Numeric In A String

Mar 17, 2008



Hi All,
I want to extract a numeric value from a string. Example, in a string like - Mgmt Pack: Processor Exception Threshold >80% Every 10 Minutes. - I want to extract that number 80. Since, later I'll want this number to plot a graph.

Since this is going to be an alert pulled from OnePoint (MOM Operational Database), the number will vary, and so I cannot look for the same number. So, can anyone help me how to get this working?

Thanks a lot in advance and let me know if the question is not clear.

Manoj Deshpande.

View 4 Replies View Related

Finding The Character Position Of Nth Occurence In A String.

Jun 15, 2001

Hi Friends:

I usually run this query in Oracle to find out the position of a character in a string from first position to the nth occurence.

For example, I run the following queries in oracle to get the desire result.

SQL> select instr('DADADAQQQA','A',1,1) FROM DUAL;

INSTR('DADADAQQQA','A',1,1) -- First occurence of 'A' from start.
---------------------------
2

SQL> select instr('DADADAQQQA','A',1,2) FROM DUAL;

INSTR('DADADAQQQA','A',1,2) -- Second occurence of 'A' from start.
---------------------------
4

SQL> select instr('DADADAQQQA','A',1,3) FROM DUAL;

INSTR('DADADAQQQA','A',1,3) -- Third occurence of 'A' from start.
---------------------------
6

SQL> select instr('DADADAQQQA','A',1,4) FROM DUAL;

INSTR('DADADAQQQA','A',1,4) -- Forth occurence of 'A' from start.
---------------------------
10


Is there ay equivelant way in Transact- SQL? If not, can anybody suggest the solution?


--Raj

View 4 Replies View Related

Built In Function To Search The Occurence Of Char In String

Jan 10, 2008



Hi Everyone-
i woder if someone know Built in function to search the occurence of char in string in T-SQL

and iam not talking about the Index of the first occurence (e.g CHARINDEX )
but it is more generic

i want to send to the function to ask about the index the second or the third or N occurence of the char.
and i wonder if it is built in for performance reasons

example
getseachIndex("M|d|d","|",2)==> the result is 4

View 3 Replies View Related

Dynamic Sort Column And Sort Order Not Working

Aug 7, 2007

I 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 

View 1 Replies View Related

Cannot Sort A Row

Oct 26, 2006

i 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!!

View 1 Replies View Related

To Sort Or Not To Sort

Dec 10, 2007

We 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

View 2 Replies View Related

SQL Sort Help

Mar 11, 2008

I'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

View 3 Replies View Related

How To Sort The Date ?

Oct 12, 2006

SELECT
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

View 2 Replies View Related

Sort Of Intersect

Feb 27, 2008

I 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

View 1 Replies View Related

Select Sort

Mar 18, 2004

I 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

View 3 Replies View Related

Need Urgent Help To Sort This Out!!

Aug 31, 2005

I'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

View 3 Replies View Related

Sort Order Id. ?

Jul 20, 2000

Hi,
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

View 4 Replies View Related

Sort Order

Jan 13, 2000

We 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 Related

Sort / Group?

Sep 21, 2000

I 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.

View 3 Replies View Related

SORT ORDER

Sep 20, 2000

HOW CAN I CHECK THE SORT ORDER OF MY OLD SERVER?

HELP

View 1 Replies View Related

Sort Orders

Oct 5, 1999

Is there a query you can run against a 7.0 server to return the chracter set and sort order.

View 1 Replies View Related

Sort Order ID&#39;s

Oct 18, 1999

I'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.

View 2 Replies View Related

How To Sort NULLs?

Jul 13, 2004

Hi 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

View 4 Replies View Related

BCP And Sort Order

Jul 17, 1998

I 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 Related

Sort Order

Jul 9, 2001

How 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 ?

View 1 Replies View Related

Sort Order

Mar 23, 2001

For SQL 2000,
Can I assign different sort order on the database level?

thanks!

View 2 Replies View Related

Sort Order Id 42 ?

Jul 24, 2000

Hi,
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

View 1 Replies View Related

How To Sort NULLS

Jul 13, 2004

Hi 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

View 2 Replies View Related

Sort MDX Query

Feb 28, 2005

Trying 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

View 1 Replies View Related

Sort Table

May 10, 2006

Hello,
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.

View 6 Replies View Related







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