query select

[yuchieh]

I am stuck in a query.

Group1 with
record1(1999),
record2(1999),
record3(2000),
record4(2000),
record5(2001),
record6(2001)

Group2 with
record1(1998),
record2(1999),
record3(1999),
record4(2000),
record5(2000),
record6(2000)

I need to select records with the maximum value in each group. So the results will be
Group1 - record5 (2001) and recrod6(2001)
Group2 - record4 (2000), record5 (2000), and record6 (2000)

I use Max(year) in where clause. But it only selects 2001 records in Group1 and shows no record found in Group2.

How should I do the query? Any help appreciated.

 

[foxdev]

But it only selects 2001 records in Group1

Isn't that what it is supposed to select? 2001 is the max in the first group.

What is the distinction between Group1 and Group2? Are these two different tables? Robert Bradley
Sr. DBA, some big company
cheap prints and oil paintings:

http://www.lowcostprints.com

 

[rajeevnandanmishra]

Hi yuchies,
I don't know what is the column name of your group.
Try to use the following code

SELECT a.* myTable a,
(SELECT myGroup, year=MAX(year) FROM myTable c
GROUP BY myGroup) b
WHERE a.myGroup=b.myGroup AND a.year=b.year


If it does not work, let us know.

 

[yuchieh]

They are in the same table.

If I do where group = 'group1', the result is correct. But if I do where group = 'group2', there is no result found. Because it's looking for year = 2001, which I don't want.

I want the year value to be dynamic also in each group. So when I do where group = 'group2', I want results "record4 (2000), record5 (2000), and record6 (2000)".

I hope I didn't confuse you.

Thanks.

 

[rajeevnandanmishra]

Hi Yu.,
Sorry i had forget to write FROM in the first line of above select statement.
You can add your group criteria in the last by adding
AND a.myGroup='myValue'