select the max value from the row and its other values
-
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.MaximumIt 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
-
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
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 -
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 -
its working :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?
Add a
top 1for example to the first select-statement. -
Add a
top 1for example to the first select-statement. -
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 P111501P110102 got two rows, how do i get one of it only?
Add a
Group by-statement to the very end of the whole query. -
Add a
Group by-statement to the very end of the whole query.it feels like im getting annoying now
group bystatement might work if i have an aggregate statement somewhere, now there isnt i cant do the group by i am tryingdistinctnow, but i get incorrect syntaxSELECT 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.Maximumthis is actual code, posnumber is project -
it feels like im getting annoying now
group bystatement might work if i have an aggregate statement somewhere, now there isnt i cant do the group by i am tryingdistinctnow, but i get incorrect syntaxSELECT 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.Maximumthis is actual code, posnumber is projectThis 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 -
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 -
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 2How 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 2I 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
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