What function(s) can be used to find the mode of data? I have a column that is populated with codes and I'd like to summarize the data by the code that occurs the most frequently. Any help is appreciated!!
View 5 RepliesIn the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don't contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2...
use LEIS
If OBJECT_ID ('dbo.temp') IS NOT NULL
DROP TABLE dbo.temp
GO
CREATE TABLE dbo.temp
( OrderNo bigint not null,
OrderNoindicator tinyint not null
[Code] .....
I have a problem finding the last occurrence of a character in a string.
The charindex seems just able to search from left to right..
I am trying to figure out how I can find the names of people who have a double occurrence or more of characters either in their first name or last name.
This is for a data cleanup.
CREATE TABLE #Names ( FIRSTNAME VARCHAR (20), LASTNAME VARCHAR (20))
INSERT INTO #Names VALUES
('Thomas', 'Hohner')
,('Mike', 'SSmith')
,('TtTony', 'Hawk')
,('Jeff', 'Smith')
,('ZZSara', 'Jones')
,('Luke', 'HendersonZZ')
,('Lily', 'PPadZZZ')
SELECT * FROM #Names
DROP TABLE #Names
I was not here for the installation of our current SQL server and those who did the install are no longer with the company. Is there a way I can figure out which mode was used? A query?
Any help would be great!
Kelli
Is there any way in which I can determine the licensing mode (per server/per seat and no. of licenses) for a particular server?
I have been working on this but no clues so far...
Thanks!
- Manoj
I want to get the top 10 most frequent cpt's for each dssid.
nuclear medicine 12345
54321
64536
87648
98356
13254
76534
87638
24364
98354
urology 63547
98745
ect...
So,
select dssid,cpt,count(*) from enc_vis_cpt group by dssid,cpt
will give me the cpt's and their frequency for each dssid.
dssid cpt count
SPINAL CORD INJURY 9934120
AMB SURGERY EVAL BY NON-MD 622703
PSYCHOSOCIAL REHAB - GROUP 993414
SPINAL CORD INJURY 983419
AMB SURGERY EVAL BY NON-MD 6327031
PSYCHOSOCIAL REHAB - GROUP 9734114
SPINAL CORD INJURY 9934280
AMB SURGERY EVAL BY NON-MD 6227353
PSYCHOSOCIAL REHAB - GROUP 9934524
How do I limit the output to just display the 10 most frequent cpt's for each dssid. Thank you...
Hello all
I'm not sure if this is anything to be concerned about, but I'd appreciate some input. I've created an alert that invokes a log backup scheduled job to start if one of the production database log becomes over 80% full.
I've noticed that the log gets backed up every couple of minutes- sometimes even more frequently- while a series of scheduled jobs are in the process of execution. Otherwise, it runs and backs up the log every hour as scheduled. I've also noticed that some of the jobs are taking longer to complete (they've been running for about 6 months now). Each job truncates the table that it populates with data, so I'm not sure what the cause of the delay is. It doesn't look like there is any fragmentation. Am I missing anything? Thanks
I have used Base SAS for analysis for a while and it was really great.. everything is easy just with a simple command.. I am sure it's not the same in SQL Server but I need some help on how to start with the following:
I have a field called call_country and another field called call_minute. Each call will be saved with the destination country and the total number of minutes..
and I want to run a query to see what are the TOP frequent destinations in this format:
United States - Count: 420 - Total Minues: 12,345
There must be a way to do this simply. We're running SQL Server 2000. I'm looking for some generic SQL statement that I can apply.
If I have a table with a person column and a location column and multiple records for the same person / locatioin combination, how do I select the person with the location they most frequently visited? Say George visits Mexico 5 times, and the Bahamas twice and costa rica once. I would have 8 records in my table for George. The data looks something like this:
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Mexico
George/Bahamas
George/Bahamas
George/Costa Rica
Ben/Brazil
Ben/Brazil
Ben/Peru
The results would be:
George/Mexico
Ben/Brazil
Thanks!
Myles
I have a reviews table where all reviews are submitted. On the main page I want to display the 10 most reviewed products. I have a Product_ID column in this table which identifys the product. How can i write a query which will select the product_ID of records which have the most frequent product_ID's?
I came up with something like this:
"Select Top 10 Product_ID, COUNT(*) AS Occurances FROM reviews GROUP BY Product_ID ORDER BY occurances DESC"
But it does not work.?? It says "Declaration expected" as error
Hi MSDN ppl,
I seek your expertise yet one other time.
Scenario:
We have 7 databases mirrored on two servers which are mirroring partners. 3 of the 7 databases are live on server1 and mirrored on server2; and the remaining 4 databases are live on server2 and mirrored on server1. The data is exposed through .NET Widows Application.
The configurations of the servers are as follows.
System: Microsoft Windows Server 2003 R2
Standard x64 Edition
Service Pack 2
Computer: Intel(R) Xeon (R) CPU
5130 @ 2.00 GHz
2.00 GHz, 32.0 GB of RAM
SQL Version: Microsoft SQL Server 2005 - 9.00.3175.00 (X64) Jun 14 2007 11:45:39
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition (64-bit)
on Windows NT 5.2 (Build 3790: Service Pack 2)
Problem:
The databases for no apparent reason keep randomly failing over to one server quite frequently. At least twice a day. There is no pattern associated for me to make out as to why this is happening.
My Questions:
1. Is it a good practice to divide the databases on each server, the way it is now? Or should all the databases be kept on one server and mirrored on other all the time?
2. From the above mentioned scenario, do you find the reason for database to 'failing over' so frequently? Could the Win Application which is used to expose the data be responsible for the failovers?
3. What steps can be taken to check for the reason which is causing the databases to failover? Alternatively and most importantly, how can I this problem of 'Databases Failing over randomly' be solved?
Thank you,
Little_Birdie
Hi guys , may I know is there any way for getting the information about the tables that most use frequently in the db?
Best Regards,
Hans
How can I make a statement that will return the 10 most frequently occuring values in a column?
I have no idea if that is even possible, if you have an idea on how I could do that I would really appreciate it.
Im trying to make a page that would show some statistics on a table I have.
Im also trying to make something that would show the count of the number of records inserted in the last 24 hours, week, month, year etc. The table has a column called "DateInserted" as SmallDate, right now i can use a Where DateInserted > '20080310' to get the count, but its not dynamic, is there anyone to merge all these results into one row with each column being a diffrent time period?
I know this a lot of questions, but I would really appreciate any pointers.
we have a handful of developers and each of us are responsible for laying out and creating our own database backends. This often leads to inconsistencies in table and column structures.One obvious situation that comes up often is whether or not the other developers are building in history into their primary tables, using history/archive tables or (usually in cases of helper tables) no historical data at all.
My thought on how to alleviate this a little was to suggest that we all build a IS_DELETED computed column into our tables so that someone else trying to work with their data doesn't have to play the guessing game. In most cases, this column would just be running date comparisons on an Expiration Date and either checking to see if it's in the future (usually 12/31/9999) or NULL.
I have read that computed columns can be a performance hit if used/returned unnecessarily but is that also the case on fields where their main use would be filtering? It just seems that the calculation that the computed column is doing would be necessary for the WHERE anywhere so it seems like a wash ... and worth the benefit of not having to decipher someone else's work.
I am working on a text mining application wherein I need to detect unusual/anomalous sentences in text. Certain sentences, that I know occur very frequently, are given a likelihood of 0.2 by PredictCaseLikelihood. Other sentences that are just as frequent get a much higher likelihood (>0.9). I am using the NORMALIZED option. The only significant difference between these sentences is their length. The one with the lower likelihood has only 2 words in it, whereas the one with the higher likelihood has more than 10 words. The problem is that the shorter sentences end up being interpreted as anomalous, when in fact they are'nt. Any suggestions?
View 2 Replies View RelatedDurning install I selected Window's Authentication only, but now it seems we may need to use a Mixed Mode with an SA account etc... is there anyway to switch SQL 2005 to use Mixed Mode after the fact?
View 1 Replies View RelatedWe have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
Hi
I am constantly getting this error message in the Application log after installing SQL 2005 last night followed by SP1 (say 5 times a minutes). See below:
EventType sql90exception, P1 reportingservicesservice.exe, P2 9.0.2047.0, P3 443f5953, P4 sqldumper_unknown_module.dll, P5 0.0.0.0, P6 00000000, P7 0, P8 00e8ed9d, P9 00000000, P10 NIL.
I have reservice packed SQL 2005 but made no difference - it is running on a Windows 2003 server with all the latest MS patches.
Does anyone know the solution or possible solution to this issue?
Thanks
Matt
Hello,everyone!!
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000
rows) But when I execute in the text mode, it takes about 0.40 seconds!!
Why is this difference?
Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!)
Why is it so slow on the text mode & relatively faster in the grid mode?
Has anyone got any idea on Firehose style cursor ?(which may speed up access of data in the VB application)
Rgds,
Adie
how to put sql server database in suspect mode intensely and get it out from suspect mode to normal mode.
i am using SQL server 2008 R2
Recently I read such statments 'When SQL Server is run in "lightweight pooling" mode (fiber mode) and the DTC service is started, unexpected behavior may occur.'
Can someone say anything about fibe mode?I am appreciated for it.:)
Hi,
Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.
Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?
Thanks very much.
Here's my table:
-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
Users can make several different orders. I want to only select the row with the first occurrence of a userID. For example:
-------------------------------
Orders
-------------------------------
UserID | Cost | Item
-------------------------------
1 | 3.00 | Box
1 | 6.00 | Candy
2 | 7.00 | Towel
3 | 5.00 | Flower
3 | 6.00 | Candy
Should only select:
1 | 3.00 | Box
2 | 7.00 | Towel
3 | 5.00 | Flower
Hi folks,
I have a SQL 2005 OTC. CTP version running on Windows 2003 server.
I would like to find out how the SQL server option changed to Windows Authentication mode from Mixed mode over the weekend. From the SQL log, I don't see when it changed. I would like to see Date/Time and client IP. If I can see User ID (windows) that would be great. Where I can find these info in SQL server?
Thank you in advance...
SHJ
This one shouldn't be too hard to do, just can't seem to figure it out.
I have two tables
tNames which contains 'ID' and 'Name'
tLocations which contains 'ID' and 'Location'
each ID may be associated with many Locations
I want to run a query to display these results:
[ID, Name, Location]
but only to return the first Location associated with that ID (or the max, min, I don't really care as long as it's only one result)
right now I have
Code:
SELECT tNames.ID, tNames.Name, tLocation.Location
FROM tNames INNER JOIN tLocations
ON tNames.ID = tLocations.ID
but this returns a new row for each different Location of the same ID
Thanks.
I have a sample table as follows:
IDITEM_EFFECTIVE_DTITEM_TERMAMOUNT
106738118-Jan-142141-200
106738102-Feb-142141-274.82
108766810-Jan-1421411238
108766810-Jan-142141-1238
108766810-Jan-142141309.5
108766810-Jan-142141-309.5
109754020-Jan-142141-3814.16
109754025-Feb-142141-100
110337727-Feb-142141-200.6
110337731-Jan-142141-19.24
I need to find rows from the above table which has the first occurence of the corresponding ID. This the output will contain the rows that will have first occurence of the following ids.
1067381, 1087668, 109754, 1103377.
How does one handle this. I have not been able to come up with the sql yet.
I have a field that contains data like "mary;john;dog;cat;frog" I want to query the field for the values between each of the semi colins. I was going to use Substring with a patIndex but I can only patIndex the first semi colin and not the 2nd 3rd.... I was wondering if someone could tell me the right was of doing this or point me in the right direction.
Thank you,
Hi,In the datawarehouse DB (under MS commerce server 2002) a table storesthe referer domain name. Table structure is likerefererdomainid <binary>,domainInternalFlag<0/1>,refererDomainName<varchar>e.g.<binary>|0|unknown<binary>|1|google.com<binary>|1|yahoo.com<binary>|1|google.com<binary>|1|google.com<binary>|1|google.com<binary>|1|altavista.commy problem is to build a query (using this table only) whichrefererDomainName has the max occurrence and how many times. As in thetable above it is google.com and 4 times.Can anyone help me.Thanks in advance.
View 1 Replies View RelatedI have the following query:
SELECT Notifications.[TimeStamp])
FROM dbo.vwGrantsMaster LEFT OUTER JOIN
dbo.CoFundNotifications ON dbo.vwGrantsMaster.GrantFMBId = dbo.CoFundNotifications.GrantsFMBId
It is returning multiple records from Notifications table since it allows multiple entries under a single GrantFMBId. For example for a single GrantFMBId there can be multiple TimeStamp. When retrieved all are appearing even distinct key word is used.
What I am looking is that to get only the first occurrence of a record for a GrantFMBId from Notifications table.
Any help?
Thank you in advance.
How to locate last occurrence of character in string?
View 1 Replies View RelatedI have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.
TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'
From the above text1, I want to get “AXNARENDR”.
Based on logic defined below:
First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”
In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string
i.e AXNARENDR (TEXT1 I defined “A” with 4th occur
TEXT 2:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'
From the above text2, I want to get “AYENDGHR”.
TEXT 3:
'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'
From the above text3, I want to get “AZENIVKHR”.
generate output as specified below. i need to get 1st value when record is duplicated in hierarchyval column
IF OBJECT_ID('tempdb..#test') IS NOT NULL
drop table #test
create table #test
(
hierarchyid int
,hierarchyval int
[code]...