Making a max query

[Enrique Rojas]

Hello,

I am trying to make a query in which I just want to show the records
with the maximum values. It sounds so simple but my sql is a really
rusty when it comes to aggregates.

table a

D_ID D_NAME LEVEL_ID YEAR1 YEAR2 YEAR3
5 TM114 2 2 2 2
5 TM114 1 1 1 1
5 TM114 0 0 1 3
24 TC201P 1 4 5 6
24 TC201P 2 5 9 9
24 TC201P 0 1 4 3


I just want the maximum Level_ID rows.

D_ID D_NAME LEVEL_ID YEAR1 YEAR2 YEAR3
5 TM114 2 2 2 2
24 TC201P 2 5 9 9

I tried selecting with max(level_id) with d_name in the grouping and
it is fine. But when I grouped by year1 the other levels show.

Thanks in advance sorry for the simple question.

Enrique Rojas

 

[Michel Walsh]

Hi,


SELECT d_id, MAX(d_name), MAX(level_id), MAX(year1), MAX(year2), MAX(year3)
FROM tableA
GROUP BY d_di



Hoping it may help,
Vanderghast, Access MVP

 

[Jerry Whittle]

There's two problems with your table as shown.

1. I see no primary key or unique columns. Without a PK, it's going to be
difficult to do. Even an autonumber field would help. With a primary key you
could create a calculated field that adds up the different year fields in
each record then use that query as the basis for your final query. Of course
if you ever add another year field, the calculation would need fixing which
brings me to the next point.

2. Your data isn't normalized as you have Years going across as in a
spreadsheet instead of down as it should in a database. If properly
normalized you could do a Sum of the Year field and group by D_ID and D_NAME
fields.

 

[Jason Lepack]

SELECT A.*
FROM [table a] AS A
INNER JOIN (SELECT D_NAME,
MAX(LEVEL_ID)
FROM [table a]
GROUP BY D_NAME) AS B
ON A.D_NAME = B.D_NAME
AND A.LEVEL_ID = B.LEVEL_ID

Cheers,
Jason Lepack

 

[Jason Lepack]

And that's why you test your code, then post....

SELECT A.*
FROM [table a] AS A
INNER JOIN (SELECT D_NAME,
MAX(LEVEL_ID) AS M_LVL
FROM [table a]
GROUP BY D_NAME) AS B
ON A.D_NAME = B.D_NAME
AND A.LEVEL_ID = B.M_LVL

Also, this assumes that there won't be any duplicates in the tuple
(D_NAME, LEVEL_ID) (it's a unique index)

SELECT A.*
FROM [table a] AS A
INNER JOIN (SELECT D_NAME,
MAX(LEVEL_ID)
FROM [table a]
GROUP BY D_NAME) AS B
ON A.D_NAME = B.D_NAME
AND A.LEVEL_ID = B.LEVEL_ID

Cheers,
Jason Lepack

Hello,

I am trying to make a query in which I just want to show the records
with the maximum values. It sounds so simple but my sql is a really
rusty when it comes to aggregates.

table a

D_ID D_NAME LEVEL_ID YEAR1 YEAR2 YEAR3
5 TM114 2 2 2 2
5 TM114 1 1 1 1
5 TM114 0 0 1 3
24 TC201P 1 4 5 6
24 TC201P 2 5 9 9
24 TC201P 0 1 4 3

I just want the maximum Level_ID rows.

D_ID D_NAME LEVEL_ID YEAR1 YEAR2 YEAR3
5 TM114 2 2 2 2
24 TC201P 2 5 9 9

I tried selecting with max(level_id) with d_name in the grouping and
it is fine. But when I grouped by year1 the other levels show.

Thanks in advance sorry for the simple question.

Enrique Rojas- Hide quoted text -

- Show quoted text -