SELECT ... GROUP BY Error
-
I have as table like TheDate Type Value I need to retreive the last value for each type. I try a SQL command like SELECT MAX(TheDate), Type, Value from MyTable GROUP BY Type But I receive an error : Erreur SQL : ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" Do you have any idea ?
I would try
SELECT MAX(TheDate), Type, Value
FROM MyTable
GROUP BY Type, ValueLobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
-
I would try
SELECT MAX(TheDate), Type, Value
FROM MyTable
GROUP BY Type, ValueLobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
Thanks for the idea, but it's not enought because the values are diffrent. I receive in this case : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/15 , T2 , 10 ... And I only need the last value for T1, T2, ... ! Mike
-
Thanks for the idea, but it's not enought because the values are diffrent. I receive in this case : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/15 , T2 , 10 ... And I only need the last value for T1, T2, ... ! Mike
Then you need to remove the Value column from the query.
SELECT MAX(TheDate), Type
FROM MyTable
GROUP BY TYPEBut then you don't get the value. So join the result from this query with the original table.
WITH maxdate AS (
SELECT MAX(TheDate) TheDate, Type
FROM MyTable
GROUP BY Type
)
SELECT t.TheDate, t.Type, t.Value
FROM MyTable t join maxdate d
ON t.TheDate = d.TheDate
AND t.Type = d.TypeLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have as table like TheDate Type Value I need to retreive the last value for each type. I try a SQL command like SELECT MAX(TheDate), Type, Value from MyTable GROUP BY Type But I receive an error : Erreur SQL : ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" Do you have any idea ?
You first need to find the MAX(TheDate) and then use that to find the Value(s). :)
select Value
,Type
from TABLE
where TheDate in ( select MAX(TheDate)
from Table
group by Type
)
;That's off the top of my head, but should be close. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
You first need to find the MAX(TheDate) and then use that to find the Value(s). :)
select Value
,Type
from TABLE
where TheDate in ( select MAX(TheDate)
from Table
group by Type
)
;That's off the top of my head, but should be close. :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
OK, but you select the max date and not the max date per type I have several types and I shearch the last value for each type and not the max date per type
MikeDhaan wrote:
and not the max date per type
The inner select has a group by type which should produce a list of MAX(dates). :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
MikeDhaan wrote:
and not the max date per type
The inner select has a group by type which should produce a list of MAX(dates). :)
Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
Correct but the result with the extract below is not correct. MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/10/10 , T3 , 15 ... The result is : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 :confused: 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/08/15 , T2 , 10 ... Because select MAX(TheDate) from Table group by Type return 2011/09/25 (for T1) 2011/08/11 (for T2) 2011/10/10 (for T3)
-
Thanks for the idea, but it's not enought because the values are diffrent. I receive in this case : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/15 , T2 , 10 ... And I only need the last value for T1, T2, ... ! Mike
Try this query
select distinct
(select top 1 t2.thedate from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as thedate
, t1.[type]
,(select top 1 t2.value from MyTable as t2 where t2.[type]=t1.[type] order by t2.thedate desc) as [Value]
from MyTable as t1
order by t1.[type]
I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.
-
Correct but the result with the extract below is not correct. MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/10/10 , T3 , 15 ... The result is : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 :confused: 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/08/15 , T2 , 10 ... Because select MAX(TheDate) from Table group by Type return 2011/09/25 (for T1) 2011/08/11 (for T2) 2011/10/10 (for T3)
Do a little research on Row_Number, Partition and Ranking. Using these you can set up an inner query that ranks the records by date partitioned over type. The outer query would then join by date and type where the rank = 1.
Never underestimate the power of human stupidity RAH
-
Then you need to remove the Value column from the query.
SELECT MAX(TheDate), Type
FROM MyTable
GROUP BY TYPEBut then you don't get the value. So join the result from this query with the original table.
WITH maxdate AS (
SELECT MAX(TheDate) TheDate, Type
FROM MyTable
GROUP BY Type
)
SELECT t.TheDate, t.Type, t.Value
FROM MyTable t join maxdate d
ON t.TheDate = d.TheDate
AND t.Type = d.TypeLight moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Correct but the result with the extract below is not correct. MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 2011/08/17 , T1 , 12 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/10/10 , T3 , 15 ... The result is : MAX(TheDate), Type, Value 2011/08/11 , T1 , 10 :confused: 2011/09/25 , T1 , 11 2011/08/11 , T2 , 15 2011/08/15 , T2 , 10 ... Because select MAX(TheDate) from Table group by Type return 2011/09/25 (for T1) 2011/08/11 (for T2) 2011/10/10 (for T3)
Since there are multiple values the same in TheDate column it can not be used as a key and that is why you are getting those results. Another, more complicated way, is to use the date and type as a key. Try this
select date
,type
,value
from table t
, ( select max(date) max_date
,type type
from Table
group by type
) tg
where t.date = tg.max_date
and t.type = tg.type
order by type
;Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Since there are multiple values the same in TheDate column it can not be used as a key and that is why you are getting those results. Another, more complicated way, is to use the date and type as a key. Try this
select date
,type
,value
from table t
, ( select max(date) max_date
,type type
from Table
group by type
) tg
where t.date = tg.max_date
and t.type = tg.type
order by type
;Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
-
Since there are multiple values the same in TheDate column it can not be used as a key and that is why you are getting those results. Another, more complicated way, is to use the date and type as a key. Try this
select date
,type
,value
from table t
, ( select max(date) max_date
,type type
from Table
group by type
) tg
where t.date = tg.max_date
and t.type = tg.type
order by type
;Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
I'm always reluctant to push the partition/rank solution as someone always seems to come up with a simpler answer.
Never underestimate the power of human stupidity RAH
-
If you get performance issues on this query you will need a composite index on (Type,TheDate)
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I'm always reluctant to push the partition/rank solution as someone always seems to come up with a simpler answer.
Never underestimate the power of human stupidity RAH
A partition/rank solution wouldn't need the join and therefore probably performs better. The simplest answer isn't always the best.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
Heres a query using Rank as suggested by Mycroft
SELECT TheDate, TYPE, Value
FROM (
SELECT TheDate, Type, Value, Rank( ) OVER (PARTITION BY Type ORDER BY TheDate DESC NULLS LAST) RN
FROM MyTable
)
WHERE RN = 1Just a bit curious whether performance is better.
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
I have as table like TheDate Type Value I need to retreive the last value for each type. I try a SQL command like SELECT MAX(TheDate), Type, Value from MyTable GROUP BY Type But I receive an error : Erreur SQL : ORA-00979: not a GROUP BY expression 00979. 00000 - "not a GROUP BY expression" Do you have any idea ?
SELECT MAX(TheDate), Type, Value FROM MyTable GROUP BY Type, Value