Database

2-Aug-05 4:56

I have two colums, WPID and WNum. WPID is distinct, but WNum can be reapeated.
For instance, I could have WPID 1-10, and each value for WNum would be 55555. What I need to do is get the maximum value for WPID associated to WNum, in this case 10.....and spin through all the records this way.

I've tried (and other ways):
SELECT MAX(WPID) AS Expr1, [WNum]
FROM WP_General_Info
GROUP BY [WNum], WPID

...but it's not working.

Frank Kerrigan2-Aug-05 5:51

2-Aug-05 5:51

SELECT TOP 1 WPID, WNum
FROM WP_General_Info
ORDER BY WPID DESC

bubberz2-Aug-05 6:36

2-Aug-05 6:36

Thanks knarf_scot! That helps!

I'm basically using this for a SQL Server Report, and am having trouble with the footer (which shows the highest/latest value for WPID). I have my grouping right and can see my latest value for WPID, but now the values in the textboxes aren't showing for the max value of the footer.

Richard Deeming2-Aug-05 6:19

Richard Deeming

2-Aug-05 6:19

Your GROUP BY clause includes the column you are trying to summarize. As a result, the query will return the maximum value of WPID for each row, which is equivalent to a simple SELECT statement.

To get the maximum value of WPID for a given WNum, use:

SELECT WNum, Max(WPID) As Expr1
FROM WP_General_Info
WHERE WNum = @WNumToFind
GROUP BY WNum

To return all values from WNum with the maximum value of WPID for each, use:

SELECT WNum, Max(WPID) As Expr1
FROM WP_General_Info
GROUP BY WNum

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

bubberz2-Aug-05 6:42

2-Aug-05 6:42

The problem I'm running into now is all the field from my child table have to be in the group by clause.
I get an error saying:
"Column "" is not valid in the select list because it is not contained in the aggregate function or the Group By clause"

Richard Deeming2-Aug-05 6:51

Richard Deeming

2-Aug-05 6:51

As the error message says, any column in your grouped SELECT statement must be in an agregate function (MIN, MAX, SUM, etc), or in the GROUP BY clause. Both of the examples in my previous post satisfy this condition.

If you need to return other columns from the row with the maximum WPID, you could use a sub-query:

SELECT
    I.WPID,
    G.WNum,
    I.OtherColumn,
    I.MoreInfo,
    ...
FROM
    WP_General_Info As I INNER JOIN
    (
        SELECT
            WNum,
            Max(WPID) As MaxWPID
        FROM
            WP_General_Info
        GROUP BY
            WNum
    ) As G
    ON I.WPID = G.MaxWPID And I.WNum = G.WNum

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

bubberz2-Aug-05 7:32

How to Know a stored procedure is existing

2-Aug-05 7:32

Richard!

Great! That's what I needed. Thanks so much!

SIJUTHOMASP2-Aug-05 1:51

SIJUTHOMASP

2-Aug-05 1:51

Hai,
How i can know a stored procedure is existing in the databse or not by writing code in the vb.net?Any way is there?I am sure somebody can show my way.
Thank You,
Rahul.P.Menon.

SoftwareDeveloper(.NET)

Re: How to Know a stored procedure is existing

Michael Potter2-Aug-05 4:10

Michael Potter

2-Aug-05 4:10

This query should point you in the right direction.

SELECT SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES

Re: How to Know a stored procedure is existing

Frank Kerrigan2-Aug-05 6:01

Re: How to Know a stored procedure is existing

2-Aug-05 6:01

This will work or something like it in SQL Server. If you wish to use this from .NET wrap this into a Stored Proc and call from ADO.NET.

#########################################

DECLARE @SPS varchar(200)
SET @SPS = 'my_stored_proc'

if exists (select * from dbo.sysobjects where id = object_id(@SPS)
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN

SELECT 'Yes it existed Smile | :)

'

END
ELSE
BEGIN
SELECT 'Cant find Proc Frown | :(

END
###########################################

SIJUTHOMASP2-Aug-05 19:30

SIJUTHOMASP

2-Aug-05 19:30

Hai Michael Potter,Knarf_scot
Really great.Thanks much for your fantastic help and showed me the right way.
Thank You,
Rahul.P.Menon.

SoftwareDeveloper(.NET)

Image Column and File Group

devvvy1-Aug-05 21:22

devvvy

1-Aug-05 21:22

Hi, this is a database/schema design question. Should I move image column to a different table, possible to a different file group on a different disk, would it improve performance (appreciable improvement)?

NOW:

<br />
CREATE TABLE tblInventoryItem (<br />
   ItemID INTEGER IDENTITY(1,1),<br />
   ItemName NVARCHAR(30),<br />
   ItemDescription NVARCHAR(30),<br />
   ...<br />
   pic IMAGE<br />
)<br />

Would this (following) improves performance?
Code:

<br />
CREATE TABLE tblInventoryItem (<br />
   ItemID INTEGER IDENTITY(1,1),<br />
   ItemName NVARCHAR(30),<br />
   ItemDescription NVARCHAR(30),<br />
   ...<br />
)<br />
<br />
-- Two columns only:<br />
CREATE TABLE tblEmployee (<br />
   ItemID INTEGER IDENTITY(1,1),<br />
   pic IMAGE<br />
)<br />

Thanks in advance.

Norman Fung

Re: Image Column and File Group

Michael Potter2-Aug-05 4:21

Michael Potter

2-Aug-05 4:21

Keep your data where it belongs. If the pic column is dependent upon the ItemID and there is only one of them per ItemID, it belongs in the tblInventoryItem table.

Just rememeber that the pic column is in the table when you construct your queries. Don't use SELECT * when a more restricted query will do. The image data is already stored in another section of the database. SQL only stores a pointer in the actual table and does the grunt work when you need the image.

Re: Image Column and File Group

devvvy2-Aug-05 17:11

devvvy

2-Aug-05 17:11

Thanks.

Norman Fung (c)

DataSet

Tiger4561-Aug-05 18:56

Tiger456

1-Aug-05 18:56

hai

I had a Dataset With table Product . Whose Fields are

productID int
Name string
Price int

I had populated the Data Set with records.

From the Data Set i need to found

+ Maximum Price 
+ Minimum Price 
+ Sum of All Price

Is there any way to solve this with out much coding

Plese help me to solve this

Thanks & Regards

Marc Soleda2-Aug-05 1:11

Marc Soleda

2-Aug-05 1:11

You can split in several queries or do it in just one:

SELECT max(Price), min(Price), sum(Price) from Product

then access the corresponding field of the result set.

Marc Soleda.

... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.

toxcct3-Aug-05 5:19

toxcct

3-Aug-05 5:19

i don't think your select will work as there isn't any Group By statement for the agregate functions you use (max(), min(), sum())...

TOXCCT >>> GEII power
[toxcct][VisualCalc]

Marc Soleda3-Aug-05 5:29

Marc Soleda

3-Aug-05 5:29

Obviously is better to split in several queries or group them but depending on the table design it'll work. Try it with the table that anig1234 provided. I did it.

Marc Soleda.

... she said you are the perfect stranger she said baby let's keep it like this... Tunnel of Love, Dire Straits.

Frank Kerrigan2-Aug-05 21:55

UPDATE cmd ASP.net Access 2k doesn't work

2-Aug-05 21:55

Try this

*** Max Price *****
select top 1 * from Product
order by price DESC

*** Min Price *****
select top 1 * from Product
order by price

*** Sum of all Prices ***
select sum(price) from Product

xsoftdev21-Aug-05 9:17

xsoftdev2

1-Aug-05 9:17

Hi folks,

My asp.net app is having trouble updating a table in Access 2K. I don't get any errors but the data is not updated. When I run the same query in Access Query IDE it runs fine. Any idea on what might be going on?

Thanks
Mike

private void btnSubmit_Click(object sender, System.EventArgs e)<br />
{<br />
// Create Instance of Connection and Command Object<br />
OleDbConnection myConnection = new OleDbConnection(ConfigurationSettings.AppSettings["ConnectionString"]);<br />
<br />
OleDbCommand myCommand = new OleDbCommand("UPDATE tblZtest SET PropertyType = " +<br />
	"@Type WHERE zTest = @PropertyID", myConnection);<br />
//			OleDbCommand myCommand = new OleDbCommand("qryzTestUpdate", myConnection);<br />
<br />
// Mark the Command as a SPROC<br />
//myCommand.CommandType = CommandType.StoredProcedure;<br />
myCommand.CommandType = CommandType.Text;<br />
<br />
// Add Parameters to SPROC<br />
OleDbParameter parameterPropertyId = new OleDbParameter("@PropertyID", OleDbType.Integer, 4);<br />
parameterPropertyId.Value = Convert.ToInt32(txtPid.Text);<br />
myCommand.Parameters.Add(parameterPropertyId);<br />
<br />
OleDbParameter parameterType = new OleDbParameter("@Type", OleDbType.Integer, 4);<br />
parameterType.Value = Convert.ToInt32(txtID.Text);<br />
myCommand.Parameters.Add(parameterType);		<br />
<br />
try <br />
{<br />
	myConnection.Open();<br />
	myCommand.ExecuteNonQuery();				<br />
	myConnection.Close();<br />
<br />
	<br />
	lblMessage.Text = "Done";<br />
<br />
	<br />
}<br />
catch ( OleDbException myException )<br />
{<br />
	string errorMessages = "";<br />
	for (int i=0; i < myException.Errors.Count; i++)<br />
	{<br />
		errorMessages += "Index #" + i + "\n" +<br />
			"Message: " + myException.Errors[i].Message + "\n" +<br />
			"NativeError: " + myException.Errors[i].NativeError + "\n" +<br />
			"Source: " + myException.Errors[i].Source + "\n" +<br />
			"SQLState: " + myException.Errors[i].SQLState + "\n";<br />
	}<br />
	lblMessage.Text = errorMessages;<br />
<br />
} // catch<br />
}

Re: UPDATE cmd ASP.net Access 2k doesn't work

Frank Kerrigan3-Aug-05 1:21