SQL: How To Display Top 5 Then Sum The Rest
Apr 6, 2006Hello,
I would like to query the top 5 best companies' sales (total sales),
then total the rest, what is the quickest and effective SQL to query
it?
Thanks in advance
ADVERTISEMENT
Display Data First Letter Uppercase Rest Lowercase
Sep 19, 2006I know you are able to display data all uppercase or all lowercase, but how do you display it First letter capital rest lower. Like a First or Last name?
View 3 Replies View RelatedAnalysis :: TOP N Plus Rest
Nov 17, 2015I need to create a dynamic top count of customers, plus the rest. I've seen many posts on this, however the difference I want is that the rest needs to be expandable to show what customers actually make up the rest category. For example:
Top
Cust1
Cust5
Cust2
Rest
Cust3
Cust4
I was thinking potentially the top/rest could be a shell dimension and then scope on these members to show the top and rest, however I haven't been able to achieve this.
How Can I Keep Only The Top Ten Rows And Delete The Rest
Dec 17, 2007I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?
View 17 Replies View RelatedRest Primary Keys?
Mar 20, 2008HiI have a database and I been inserting some dummy data into it but now I want to upload it to my website but I want to delete all the dummy data and start the PK back at 1. I truancted all the data but it still keeps counting from the last one.So how do I reset it?
View 6 Replies View RelatedT-SQL (SS2K8) :: How To Return Top N And Sum Up The Rest
Feb 10, 2015I am looking for an elegant way to return top n and sum up the rest, in my case CTE/temp table/variables are not allowed.
View 9 Replies View RelatedPulling Out PO# From The Rest Of The Job Descrip.
Apr 29, 2008Hello, I am new here and to sql so please bare with me.
I am trying to create 2 select statements from the same field. The field is a job_description field that sometimes contains PO#'s. One select statement needs to extract the PO# if it has been written in the field and the other needs to extract all data that is not the PO#
Here is some example of the data:
New shirts for 2007
Magazine cover for may edition
Way to go postcards PO# 45687
PO#879
For the first select statement I need to capture just the PO#. the result for the above examples would be:
NULL
NULL
45687
879
For the second select statement I need everything but the PO#. The result would be:
New shirts for 2007
Magazine cover for may edition
Way to go postcards
NULL
So far I have put together a basic idea for the PO# part:
(SELECT SUBSTRING (job_description , (SELECT patindex('%PO#%', job_description)+3),10)WHERE job_description LIKE '%po#%')
If i could figure out a way to only return numeric data from the above i think it will work.
As for the second select statement i am at a loss on how to select everything but the PO#.
Any help at all is very much appreciated.
Thanks
If One Of The BCP Fail. Does It Affect The Rest Of The Process??
Dec 8, 1998Hi, I am wrote the following code in one store procedure called p_bcp_all.
and then scheduled it to run over night. what if the first two bcp were successful but the third one failed. Is the whole procedure going to fail? also what if the first one failed, is the rest of the code going to be executed have the bcp process going for the second and the third table?
thanks for your adivce
regards Ali
create procedure p_bcp_all as
Exec master..xp_cmdshell "bcp servername..tblone in d:blone.txt /fd:formatfileblone.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tbltwo in d:bltwo.txt /fd:formatfilebltwo.fmt /servername /Usa /password/b250000 /a8000"
Exec master..xp_cmdshell "bcp servername..tblthree in d:blone.txt /fd:formatfileblthree .fmt /servername /Usa /password/b250000 /a8000"
GO
Capitalizing First Letter, And Lower The Rest
Oct 25, 2006I am trying to get this code to work: (left(religion,1))+ lower(right(religion,len(religion)-1))As Religion
I am getting this error:
Invalid length parameter passed to the RIGHT function.
With User Sa I Can See Mot Databases How Can The Rest Of The Users See Them All?
Jun 26, 2007I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Thanks in advance
brgds
Miguel
T-SQL (SS2K8) :: Take ID Value From Maximum ID And Compare Rest ID Value From Table
Jul 2, 2014--drop table #temp
create table #temp (id int, idvalue int)
insert into #temp(id,idvalue)
select 1095,75
[code]...
I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.
Retaining Records Of Top N Rows And Deleting The Rest
May 15, 2008Hi All,
I am writing a SP where I need to pass an value to maintain records of last n days. In this SP I am deleting a couple of tables based on the value passed to this SP. For e.g. If the SP is passed the value 10, then only TOP 10 records is maintained, the rest are deleted.
I have formed the following logic, which I feel can be improved vastly.
I create a temp table and
CREATE TABLE #TempAuditTbl (Rownum int PRIMARY KEY, Orderid uniqueidentifier)
INSERT INTO #TempAuditTbl
SELECT ROW_NUMBER() OVER (ORDER BY orderdate desc) AS rownum, Orderid FROM Orders
DELETE Orders FROM Orders INNER JOIN #TempAuditTbl adt ON adt.Orderid = Orders.Orderid AND rownum > @TopnRows
DROP TABLE #TempAuditTbl
OR
DELETE FROM Orders WHERE orderid NOT IN ( SELECT TOP @TopnRows OrderID FROM Orders ORDER BY OrderDate desc)
This way I am able to keep the top n records.
Which of these two solutions is more efficient? Is there a more efficient way to achieve the same.
Please help.
Thanks & regards
Sunil
Delete/Move Certain Info From Column - Leave The Rest?
Nov 10, 2005I have a column that contains extra info that needs to be moved to another column or deleted alltogether. is there a way to select these items and move them to another column, leaving the rest of the data in the original column?
EXAMPLE
MYTABLE >COLUMN1
May have Data Like: ABCDE123 SER1 or XYZ12DEFSer1:1
WHAT I NEED TO DO IS
Move anything after the SER1 to a new column and retain the rest of the data in the original column. making it look something like this:
COL1 COL2
ABCDE123 SER1
XYZ12DEF Ser1:1
Another question is if there is a way to delete extra spaces? Like make all data that has two or more extra spaces, just single spaces and any additional spacing after a row of data, delete all additional spaces after the last letter/character/number.
One more question - What would be a great resource to learn MS SQL in more depth?
I am trying to learn ASP/VBScript/JavaScript/ and now MS SQL all at once b/c this is what my business depends on. Trying to be 5,000 hats at once can get confusing and overwhelming, so I am looking for any "Crash course" I can to learn as much, as fast as possible. Any direction or ideas?
Thank you.
How To Commit The Rest Of The Transactions Of An Update In Large Bulk?
Feb 8, 2004I have to modify the table structure where the table have a lot of data already. The log is getting full due to uncommitted transactions, there is a lot of data being updated in large bulks, not all of the transactions are committed, the update task cannot be completed.
However, there is no more spare disk space for it to commit the transaction. Anyone can help?
Friendly Way To Look At Sqlagent Ssis Messages Without Exporting Rest Of Log
Apr 13, 2008I just started playing with sqlagent and am finding that viewing a specific message from an ssis step is cumbersome when other entries are also in the log. Exporting seems only able to export all history entries. Viewing right in the log viewer leads to a series of boundary expansions and right scrolls that dont seem convenient for quick identification of a problem in a medium sized ssis message. Right clicking on the message in the viewer seems like it is preparing the message for possible copy but it seems to end there. I'm already familiar with the different levels of verbosity available in the command line. Dont see anything useful on the web.
Is there a user friendly way to copy the contents of a specific message for perhaps pasting to notepad , so that problem identification is facilitated? Is there a not so user friendly way available, one taht doest involve 1st exporting the entire log?
SQL Server 2008 :: Using Left And Charindex To Parse String / Getting Rid Of Rest Of Data
Jun 16, 2015I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
Reporting Services :: Expression To Change All Caps To First Letter Uppercase And Rest Lowercase
Aug 1, 2015The states in this report are all in caps TEXAS, CALIFORNIA, etc.. Is there a way to use expressions to only have the first letter in uppercase and rest in lowercase?
View 3 Replies View RelatedReporting Services :: Display Columns When There Is No Data To Display
Apr 30, 2015I would like to display a portion of report where there is data or no data
There is data subreport display
Product Name Latex Gloves
Product ID
xxxx5678
There NO data in the subReport
Product Name
Product ID
SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE
Apr 16, 2015If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE
SQL 2012 :: SSRS - Put 10 Rows On First Page And Rest (1000+) Rows On Second Page
May 1, 2014Is there any way to control this scenario, I know trick to put 10 on each row ,but I need to split them unevenly, 10 on first page and the rest on second page. is it possible ?
=Ceiling((RowNumber(Nothing)) / 10)
How To Display This?
Dec 7, 1999I have a table with the following structure and data
SampleTable
id name parent_id
1 Microsoft 0
2 Visual Studio 1
3 J++ 2
4 VB 2
5 C++ 2
As you can see, a record with parent_id=0 is the top level. Other levels will have a parent. I want to display the above data as below
1 Microsoft
2 Microsoft - Visual Studio
3 Microsoft - Visual Studio - J++
4 Microsoft - Visual Studio - VB
5 Microsoft - Visual Studio - C++
I tried many times with the select statement but with no luck. Do you have any ideas? Thanks,
Display Another Value
Dec 5, 2011i have a column name "Principle" and the data in this column is a dollar value. How do i write a query where when the dollar amount is <0 it displays as - and when its >0, it displays as +
View 1 Replies View RelatedHow Do I Display The Day Name Instead Of The Day #
Apr 6, 2004Hi,
I created a Time Dimension and ended up with the following (as shown in the attached image). The problem is that when I go to the Day level, I see numbers e.g. 1, 2, ..., 31. Instead I want to display the Weekday Name e.g. Monday, Tueday and so on (i.e. what we get with the datename(dw,<date>) function. Right?)
However I am not sure what to change and where in order to get the desired results. Can someone kindly guide me.
Thanks.
How To Display..
Apr 21, 2008Hi,
I need to show the value of a variable with comma seperated.
For eg: I have a variable N whose value is 123456.89
I need to display it as 1,23,456.89
How i can i display this...
Please help me
Thanks in advance
How To Display?
Sep 18, 2007Hi,
I have the following stored procedure
CREATE PROCEDURE dbo.test(
@number varchar(128) output
)
as
select @number = * from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
return 0
Then I go in and execute it like this:
declare @number varchar(128)
exec dbo.test @number output
select @number
I get the following result:
T_PORTWARE_GBL
(Only one row returns).
However when I execute the sql statement below:
select gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
which is the same one as in the stored procedure except the variable declaration it reurns me the following:
gw_instance
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
(99028 rows of data)
Why is this hapenning?
Can anyone help me?
Thanks and Regards,
Sudip
How To Display
Mar 12, 2008Hi,
My table "Branch" contain a Field name as MassMail
it contains mail IDs a@yahoo.com;b@yahoo.com;c@yahoo.com;d@yahoo.com;e@yahoo.com
Now plz write me what is the SQL Statement for Retiveing MassMail
except one or Two Mail ID
Example : Retrive
a@yahoo.com;b@yahoo.com;d@yahoo.com;e@yahoo.com
Here c@yahoo.com is not Include
Thanks
Thanks
How To Display??
Sep 18, 2007
Hi,
I have the following stored procedure
CREATE PROCEDURE dbo.test(
@number varchar(128) output
)
as
select @number = gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
return 0
Then I go in and execute it like this:
declare @number varchar(128)
exec dbo.test @number output
select @number
I get the following result:
T_PORTWARE_GBL
(Only one row returns).
However when I execute the sql statement below:
select gw_instance from fix_lvts..fix_exec_reports
where gw_instance like '%PORTWARE%'
which is the same one as in the stored procedure except the variable declaration it reurns me the following:
gw_instance
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
T_PORTWARE_GBL
(99028 rows of data)
Why is this hapenning?
Can anyone help me?
Thanks and Regards,
Sudip
How Do I Display A Count?
Mar 15, 2007I want to display the number of records for each state in a database. I'm using a strongly types dataset. In my method, I have the followingSELECT COUNT(ID) AS iTotal, STATEFROM membersWHERE (Suspend = 0)GROUP BY STATEORDER BY STATEIn the code behind of my page I have Dim mateAdapter As New WAPTableAdapters.membersTableAdapter Dim mates As WAP.membersDataTable Dim mate As WAP.membersRow mates = mateAdapter.GetDataState For Each mate In mates Select Case mate.STATE Case "AK" LabelAK.Text = mate.ID. End Select Next What should LabelAK.Text = mate.ID. be for me to be able to display the number of records that have 'AK' in the state field?Diane
View 7 Replies View RelatedHow To Display Error
Dec 16, 2007 Hai, My condition of the program is to check whether the username is available or not. If it is available, I should insert that username into the db, if not I should display "Username already exists". I've written SP for the condition, but I dunno how to display the error message in asp.net. I've my codings below: DB - SP:alter procedure insert_user@username varchar(20)asif exists(select username from test where username=@username)print 'Username already exists'elseinsert into test(username) values(@username) Default.aspx.cs: protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection insertconn = new SqlConnection("Server=achuthakrishnan;Initial Catalog=classifieds;Integrated Security=SSPI");
SqlCommand insertcmd = new SqlCommand("insert_user", insertconn);
insertconn.Open();
insertcmd.CommandType = CommandType.StoredProcedure;
insertcmd.Parameters.AddWithValue("username",TextBox1.Text);
SqlDataReader dr;
dr=cmd.ExecuteReader();
insertconn.close();
} What is the condition I should check after SqlDataReader dr? Or else, I believe I should use @@error to display error messages in asp.net. I googled a lot and couldn't find the exact solution. Can any one help me?
Display ROW As A COLUMN
Mar 15, 2008I am JOINing a Table with a View, and the View can have multiple rows relating to the join condition(i.e. My view as 4 columns, and can look like the following:
A B C D
A E F G
B H I J
I want this to look like -
A B C D E F G
B H I J
I want one row, but not one column, I need to maintain each value in its own column, withinin a single row.
Doesn't matter to me if that transition happens in my View or as the outcome of my Join.
I've been looking at the PIVOT command, but that appears to only work with an aggrgrate function. I don't want to calculate/aggreagte anything, just combine my multiple rows in one.
This is my VIEW stmt -
SELECT [PKG ID] AS PKG_ID, [APPR GRP NAME], [USRID OF APPR], [APPR STATUS]FROM dbo.pkgapproversWHERE ([APPR STATUS] = 'approved') AND ([APPR GRP TYPE] = 'E')
This is my Join stmt - set @sql = N'SELECT DISTINCT [PKG ID] AS PKG_ID, [ELM ACT] AS Action, [END EXEC DATE] AS "Exec Date", [COMMENT] AS Comment, [USRID OF APPR]
FROM [PkgAction]
FULL JOIN vw_PkgApprovals
ON PkgAction.[PKG ID] = vw_PkgApprovals.[PKG_ID]
WHERE 1=1'
Any thoughts....Thanks
Display Of SqlDataSource
Mar 5, 2006I'm trying to follow along with lesson07 Web Development Express series and when I drag the SqlDataSource tool onto my form I do not see the object on the screen. I therefore can not configure it until I have placed GridView object onto my form. I hope you can help me in how to view the SqlDataSource or inform me where I can get further information
View 2 Replies View RelatedDisplay Of Images
Jan 22, 2001I want to publish Images in an asp-web-page. How can I do that?
View 1 Replies View RelatedIsql Display
Aug 9, 2000Hi,
When I execute a script by using isql, it displays the number of statement executed. can anybody tell me how to turn this off (no display)?
Thanks,
Mark