Cant figure out this query

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.This works...

SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:

select a.sk, max(a.mk) as MK from yourtable a
group by a.sk

cant enter data in field

I have a field in my table that is varchar:1500:null, but I cannot enter any more that 994 characters. any ideas why?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Quotes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Quotes]
GO

CREATE TABLE [dbo].[Quotes] (
[QuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[DateAdded] [datetime] NULL ,
[CustomerID] [int] NOT NULL ,
[ProductName] [varchar] (100) NULL ,
[RepID] [int] NULL ,
[QuoteNumber] [varchar] (30) NULL ,
[QuoteDate] [datetime] NULL ,
[QuoteTerm] [varchar] (10) NULL ,
[QuoteFOB] [varchar] (15) NULL ,
[QuoteNAIRep] [varchar] (30) NULL ,
[QuoteExpiration] [datetime] NULL ,
[Note] [varchar] (700) NULL ,
[Comment] [varchar] (1500) NULL ,
[OrderRequirement] [varchar] (1000) NULL ,
[Status] [varchar] (1) NULL ,
[DateClosed] [datetime] NULL ,
[ProductType] [varchar] (30) NULL ,
[ImageID] [int] NULL ,
[CloseMonth] [int] NULL ,
[CloseYear] [int] NULL ,
[ClosePercent] [int] NULL ,
[Segment] [varchar] (50) NULL ,
[AccountID] [uniqueidentifier] NULL ,
[ReplacedQuoteID] [int] NULL ,
[Lead] [varchar] (80) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Quotes] ADD
CONSTRAINT [DF_Quotes_Closed] DEFAULT ('N') FOR [Status]
GO

CREATE INDEX [idx_RepID] ON [dbo].[Quotes]([RepID]) ON [PRIMARY]
GO

CREATE INDEX [indx_CustomerID] ON [dbo].[Quotes]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [indx_QuoteID] ON [dbo].[Quotes]([QuoteID]) ON [PRIMARY]
GO

CREATE INDEX [indx_Status] ON [dbo].[Quotes]([Status]) ON [PRIMARY]
GO

[Comment] [varchar] (1500) NULL , is where I can't enter more than 994 characters.

note - I know very little about SQL, I just had the responsibility placed on me at my job. If this is not enough information to go on to throw an idea at me please let me know what else you need.

Thanks in advanceNo such issue i just ran your script and inserted 1500 characters into the comments column....also when you define 1500 it will give you a capacity of 1500

are you using the insert statement or just entering data through enterprise manager?? cuz insert statement worked fine for me...
insert into quotes (Customerid, comment)
values (3,'Your comment goes here')

Also try to find the length of your largest column by using the following script

select len(comment)
from quotes|||i am entering the text manually. Don't know how to run an insert script.

I ran the sel script and the longest entry is 1000 chars.

would you mind telling me how to run the insert script to apply the comment to quote number 070530JB185438?|||I was able to insert the text I need using the insert script you gave, I just had to add more fields to make it apply to the correct quote...

INSERT INTO Quotes

(QuoteID, CustomerID, QuoteNumber, Comment)

VALUES ('35845', '5695', '070530jb185438', 'comment')

Thanks for the help, it is greatly appreciated.:beer:|||since quoteid is generated automatically you dont need to insert a value there...... according to your table design then customerid is the only non nullable field which needs to be present...

hence for testing purpose if you provide a query as
INSERT INTO Quotes
(CustomerID, Comment)
VALUES ('5695', 'comment')
it would still work...since all other columns are nullable......

Also to test your case you can insert a very long string in place of 'comment'... and inside single quotes of course...

other thing what you can do to test the case is use the update statement
update quotes
set comment = 'You comment goes here'
where customerid = '5695'

this will update the record which has customerid = '5695'
i.e. the record which you have just inserted in the previous post|||Here's a function to insert a 1500 character string of x's into your comment field.

INSERT INTO Quotes
(customerid, comment) VALUES ('1234', REPLACE(SPACE(1500), ' ', 'x'))

then check the length with the Len function

Can't Edit Job in SSMS

We just started having a problem where when we right click on a job in SSMS
and select properties, it doesn't go to Edit Job, it just opens a New Job
window that is all blank. It is happening on several workstations, but not
when we use SSMS on the actual server.
Is this a known issue?Yes, I believe it was fixed with SP2. Get your clients up to date.
"Dan" <dantheriver@.newsgroup.nospam> wrote in message
news:B15DE092-1E07-4D30-A213-C7BA70912629@.microsoft.com...
> We just started having a problem where when we right click on a job in
> SSMS
> and select properties, it doesn't go to Edit Job, it just opens a New Job
> window that is all blank. It is happening on several workstations, but not
> when we use SSMS on the actual server.
> Is this a known issue?|||Hi,
It is hard to say that this is a known issue only according to the issue
appearance. However I do agree with Aaron on that you should install SQL
Server 2005 SP2 first to see if this issue can be resolved. SQL Server 2005
SP2 has many hotfixes for SSMS. You can run SELECT @.@.VERSION to check if
your SQL Server 2005 is with SP2. If the value is less than 9.0.3042, it
indicates that SP2 is not installed.
You can download SQL Server 2005 SP2 from the following link:
http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-
8f0c-63fa18f26d3a&displaylang=en
If you have any other questions or concerns, please feel free to post back.
We are very glad to assist you further.
Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||> SP2 has many hotfixes for SSMS. You can run SELECT @.@.VERSION to check if
> your SQL Server 2005 is with SP2.
Remember that some of the clients may only have the tools installed, so
running @.@.VERSION (against the only server they connect to) is misleading...
the server may very well respond 9.00.3042 while their local tools are in
fact still at 9.00.1399. So they should check Help|About to make sure their
client tools are up to date.|||Yes, Aaron, you have a more considerate thinking. It is the best practice
for checking the tool's version.
Thank you!
Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Cant Create Subscription...

I've got a strange problem that I can't quite figure out. Whenever I go to setup a new subscription for a report (any report) I can't select a schedule or create the subscription.
I get to the Subscription page, but pressing any of the buttons: "Ok", "Cancel", "Select Schedule" does nothing.
Anyone else ever have this issue?
Any feedback would be greatly appreciated.

Hi
can you able to see SubscriptionProperties.aspx Page after clicking on New Subscription.
Thanx
Rohit|||

Yeah, pressing "New Subscription" takes me to the form (SubscriptionProperties.aspx), but not matter what button I press on the form - Ok, Cancel, Select Schedule... nothing happens.
It's as if you were creating an .aspx form and set "AutoPostBack" on a control to false. Clicking on any of the buttons does nothing. Therefore, I'm not able to create a new subscription or alter
any of the subscriptions schedules that I currently have.
I've restarted the reportserver service and I've restarted the application pool. Neither helps.

|||

Just wanted to follow up on this in case that someone else runs accross this. This was a framework issue with SP1. Not really related to Reporting Services, but possibly relevant nonetheless.

http://channel9.msdn.com/showpost.aspx?postid=21650

Cant Create MSDE Database using ASP.NET Matrix

Hi i am having problems creating a new MSDE database when using the
ASP.NET Web Matrix to create it.
It says to click on New Connection, select SQL Sever Authentication,
add in the username and password and then click create new database.
I get an error though saying
Unable to connect to database server
SQL Server does not exist or access denied
ConnectionOpen(Connect())
Any suggestions?
Perhaps you didn=B4t setup the right network settigns:
http://www.codeproject.com/database/ConfigureMSDE.asp
HTH, Jens Suessmeyer.
|||Nope still doesnt seem to be letting me create a new database through
the ASP.Net Matrix.
I removed and then reinstalled the MSDE following the link you gave me,
then set it up following their stages.
I can create a database through the cmd.exe but still having the
problems with ASP.Net Matrix.
Going to remove and reinstall the .net matrix and see if that works.
|||Hi there, managed to get everything working now. deleted the .net web
matrix and the MSDE off my computer. then reinstalled everything again.
the version of .net web matrix i had seemed to be out of date so maybe
this is what was causing the problem.
Thanks for your help.

Cant create index on view

Hi,

SQL Server 2005 (SP2) Developer edition on XP Pro

Can anyone help with setting up an indexed view? Part of the select statement calls a couple of very simple functions which are deterministic. The functions which are shown below do not perform any aggregation.

This is the error message.

Msg 8668, Level 16, State 0, Line 2
Cannot create the clustered index 'IX3_SA' on view 'XPS.dbo.SA_INDEXED' because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.

Function definitions:

create FUNCTION [dbo].[MonthName](@.MonthNo TINYINT)
RETURNS CHAR(3)
with schemabinding
AS
BEGIN
DECLARE @.MonthName CHAR(3)
IF @.MonthNo BETWEEN 1 AND 12 SET @.MonthName = SUBSTRING('JanFebMarAprMayJunJulAugSepOctNovDec',((@.MonthNo*3) -2),3)
RETURN ISNULL(@.MonthName,'')
END

create FUNCTION [dbo].[FinancialYear](
@.Year INT,
@.MonthNo TINYINT,
@.YearEndMonthNo TINYINT)
RETURNS INT
with schemabinding
AS
BEGIN
DECLARE @.FinancialYear INT
IF @.MonthNo <= @.YearEndMonthNo
SET @.FinancialYear = @.Year
ELSE
SET @.FinancialYear = @.Year + 1
RETURN @.FinancialYear
END

The first part of the select statement is as follows:

(SELECT
count_big(*) as CB,

dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS REPORT_FINANCIAL_YEAR,
dbo.MonthName(MTH.Number) + ' ' + CAST(YR.Number AS CHAR(4)) AS REPORT_MONTH,
dbo.MonthName(YEAR_END_MONTH) + ' ' + CAST(dbo.FinancialYear(YR.Number,MTH.Number,YEAR_END_MONTH) AS CHAR(4)) AS REPORT_FINANCIAL_YEAR_END,

I can create the index on this view if I comment out the calls to the functions so I know that is where the problem is. I can't however see why this is erroring as both only return a single value without any agregation.

Anyone got any ideas on this?

Thanks in advance

David.

Well that met with a deafening silence.

The answer was to move these function calls out of the indexed view definition into a standard view that sits above it.

Can't create excel file with SQL Server 2000 client.

Hi ;

I am trying to create several Excel sheets using SQL 2000 views like so:

Select * INTO [Excel 8.0;Database="C:\spreadSheets\aNew.xls"] FROM [aView].

When I try and execute this in my app I get the following - Specified owner
name 'Excel 8.0;Database=c:\spreadSheets\aNew.xls' either does not exist or
you do not have permission to use it.

If I use the above Select statement with an OLEDB connection it works.

I am using Imports System.Data.SqlClient, instantiating a new SQlConnection
object, opening the connection, etc..

Thanks,

Gordon

This special select into sql statement syntax only works if you use the Jet OLEDB Provider, it's not supported by SQL Server.

So you need to open a connection to some Access database (even a dummy one will work) and do something like this:

I wrote this KB article about a decade ago (arg where does the time go) that demos the syntax:

200427 How To Export and Import Access Tables Using DAO or ODBC
http://support.microsoft.com/default.aspx?scid=kb;EN-US;200427

Here is some demo code that uses .NET:

System.Data.OleDb.OleDbConnection conn;

System.Data.OleDb.OleDbCommand cmd;

conn = new System.Data.OleDb.OleDbConnection(@."Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\junk.mdb;");

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "select * into [Excel 8.0;Database=C:\\Book1.xls;].[MyJunk] FROM [ODBC;Driver=SQL Server;Server=mySQLServer;Database=Pubs;Trusted_Connection=Yes;].[authors]";

cmd.ExecuteNonQuery();

However note there are tons of annoyances with using Jet driver and Excel, the Jet and Excel teams just never seem to want to make this any easier for the customer. The other way to do this is poke values into Excel speadsheet using ole automation (which used to be easy cheesy in VB6 but now is a nightmare in .NET since .NET deprecated COM). With newer version of Excel 2007 I wonder maybe this is easier in .NET.

Cant connect to database?

When I try to connect to ms server like this:

private DataSet hentVare()
{
string sql = "select * from vare";
string strForbindelse="server=localhost;trusted_connection=true;database=mericKebab;uid=ASLAN;password=;";

SqlConnection objConnect = new SqlConnection(strForbindelse);

SqlDataAdapter dataAdapter = new SqlDataAdapter(sql,objConnect);

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet,"Vare");

return dataSet;
}

I get this error:

System.Data.SqlClient.SqlException: Cannot open database requested in login 'mericKebab'. Login fails. Login failed for user 'ASLAN\ASPNET'.

can someone plz help me with this?Since you are using trusted SQL Server connection and the IIS virtual directory is running under the ASPNET account the application is trying to connect using that account. Try changing the IIS virtual folder to a valid domain user account that has rights on the SQL Server database you are connecting or change the authentication to SQL Server authentication and give a valid User name and password.

For more on connection strings... Visit www.ConnectionStrings.com