Database

Re: select the max value from the row and its other values

18-Dec-07 1:19

Here is the table

Project        	col1	col2	col3	col4
A        	151,46	80	0,29	2
A        	529,14	83	0,29	1
B        	3391,3	80	0	2
B        	3706,2	75	0,06	1
C        	8685,3	78	2,41	1
C        	9005,2	71	0	2

How do I get the max value for col1 for each project and its other values?

ie. project a's max value is 529,14. its others value is 80, 0.29 and 2

Project        	col1	col2	col3	col4
A        	529,14	83	0,29	1
B        	3706,2	75	0,06	1
C        	9005,2	71	0	2

I have this query, but it results everything, not only the max value for each project

select project, max(col1), col2, col3, col4 from out_pumptable group by project, col2, col3, col4

Tobias Schoenig18-Dec-07 1:41

Re: select the max value from the row and its other values

18-Dec-07 1:41

Try

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum FROM projects GROUP BY project)g WHERE MAX(col1) = g.Maximum

Deques18-Dec-07 1:51

Re: select the max value from the row and its other values

18-Dec-07 1:51

it looks good, but the column project you have after group by isnt used anywhere. is it something missing before group by?

never mind about that.

I have tried the query and it results a little too much. it seems it loops for each max value and result for all row

Tobias Schoenig18-Dec-07 1:59

Re: select the max value from the row and its other values

18-Dec-07 1:59

You're right - it has to be like that:

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum, project FROM projects GROUP BY project)g WHERE MAX(col1) = g.Maximum

Deques18-Dec-07 2:05

Re: select the max value from the row and its other values

18-Dec-07 2:05

It still doesnt work.
It says an aggregate cant be in WHERE clause

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Tried with Having and it gives a lot other errors

Tobias Schoenig18-Dec-07 2:10

Re: select the max value from the row and its other values

18-Dec-07 2:10

Sorry my fault, i copied the wrong string:
This should be the right thing:

SELECT g.Maximum, * from Projects, (SELECT MAX(col1) as Maximum, project FROM projects GROUP BY project)g WHERE col1 = g.Maximum <br />

Deques18-Dec-07 2:17

Re: select the max value from the row and its other values

18-Dec-07 2:17

its working Big Grin | :-D

, just one more thing >.<<br mode="hold" />
some rows in col1 has same value, is it possible to just have one of the values, just take from one row?

Tobias Schoenig18-Dec-07 2:22

Re: select the max value from the row and its other values

18-Dec-07 2:22

Add a top 1 for example to the first select-statement.

Deques18-Dec-07 2:31

Re: select the max value from the row and its other values

18-Dec-07 2:31

top 1 seems not be working good for this

I get this result, (partially), from the query you gave me

3320,9	P110101        
3320,9	P110101        
3220	P110102        
3220	P110102        
529,14	P110301        
3706,2	P111501

P110102 got two rows, how do i get one of it only?

Tobias Schoenig18-Dec-07 2:32

Re: select the max value from the row and its other values

18-Dec-07 2:32

Add a Group by-statement to the very end of the whole query.

Deques18-Dec-07 2:43

Re: select the max value from the row and its other values

18-Dec-07 2:43

it feels like im getting annoying now

group by statement might work if i have an aggregate statement somewhere, now there isnt i cant do the group by

i am trying distinct now, but i get incorrect syntax

SELECT  g.Maximum, distinct posnumber from out_pumptable, (SELECT  MAX(flow_lpm) as Maximum, posnumber FROM out_pumptable GROUP BY posnumber)g WHERE flow_lpm = g.Maximum

this is actual code, posnumber is project

Tobias Schoenig18-Dec-07 2:49

Re: select the max value from the row and its other values

18-Dec-07 2:49

This works for me:

SELECT g.Maximum, out_pumptable.posnumber from out_pumptable, (SELECT MAX(flow_lpm) as Maximum, posnumber FROM out_pumptable GROUP BY posnumber)g WHERE flow_lpm = g.Maximum group by g.Maximum, out_pumptable.posnumber

Deques18-Dec-07 2:53

Re: select the max value from the row and its other values

18-Dec-07 2:53

Thanks a lot and thanks for your time. I appreciate this

Rupert Todd19-Dec-07 9:36

Rupert Todd

19-Dec-07 9:36

I believe you need to join the table to a subset of itself.

You create a subquery that gives you the values of project and the highest col1 and attribute that to a temptable. You then join you orignal table to your temp table on the values of col1. This should do the trick!

SELECT PT.* FROM out_pumptable PT join
(select project, max(col1) as MaxVal from out_pumptable group by project) TEMPTABLE1 on TEMPTABLE1.MaxVal = PT.col1

calling a storedprocedure from a function in mysql

sepel18-Dec-07 0:35

sepel

18-Dec-07 0:35

hi all.
i have a stored procedure that get 2 parameter.
i want to select a special filed from my store procedure.
can i call my procedure from a function and how i should do it.
(i need some code).

sepel

Database Timeout!!!!

__yash__18-Dec-07 0:19

18-Dec-07 0:19

I got a "Database Timeout" error.
I m using an MFC application with MSDE database.
What can be the reason(s) for this??

Pete O'Hanlon18-Dec-07 0:26

Pete O'Hanlon

18-Dec-07 0:26

An operation couldn't be completed in the database in the timescale expected. Simplistically you can think of there being two potential timeouts - one when you can't connect to the database in a reasonable amount of time (known as a connection timeout) and the other when you can't complete a database operation in a reasonable amount of time (known as a command timeout).

Deja View - the feeling that you've seen this post before.

My blog | My articles

__yash__18-Dec-07 0:41

18-Dec-07 0:41

But why should a database transfer take so long when transferring on a local database????
I mean what are the possibilities????

Pete O'Hanlon18-Dec-07 1:13

Pete O'Hanlon

18-Dec-07 1:13

There are all sorts of possibilities, from slow rebuilding of indexes (every time you do an insert/update/delete) indexes are reevaluated to single action updates that should be performed as set updates.

You need to use the SQL Profiler to see what is happening on this database.

Deja View - the feeling that you've seen this post before.

My blog | My articles

__yash__18-Dec-07 1:29

18-Dec-07 1:29

Pete,
there is another problem that i face quite often.
i transfer like 20-30 rows to the database it happens fast.
But as the database size increases(1500 rows or so), insertion of 20 rows takes 20 seconds!!!!!

my program is like :

for(i=0;i<=row_count;i++)<br />
{<br />
m_pSet->AddNew();<br />
//then all the columns<br />
//e.g. m_pset->column1=...<br />
m_pSet->Update();<br />
m_pSet->Requery();<br />
}

David Crow19-Dec-07 2:29

David Crow

19-Dec-07 2:29

yashveer wrote:
m_pSet->Requery();

Is this statement absolutely necessary?

"Normal is getting dressed in clothes that you buy for work and driving through traffic in a car that you are still paying for, in order to get to the job you need to pay for the clothes and the car and the house you leave vacant all day so you can afford to live in it." - Ellen Goodman

"To have a respect for ourselves guides our morals; to have deference for others governs our manners." - Laurence Sterne

__yash__20-Dec-07 0:39

20-Dec-07 0:39

I dont have much of an idea myself. I used 'SAMS Teach yourself VC++ in 21 days' where they had given a database example(that's how i got started 1 year back).
But they did not use a 'for loop'. they were just adding one row on one click of a button.
Now even i wonder if 'Requery()' is necessary.
What if i put it outside the 'for loop' so that it executes only once?
From what it looks like in MSDN Requery() is used to Refresh the Recordset
but Update() seems to be absolutely necessary after every row is added.