SQL group by query question

[ironicbionic]

hi, table as follows:


ID DESC VALUE
======================
1 g56 7
1 j78 6
1 n67 1
1 7hh 1
2 3df 3
2 9kk 3
2 6gh 9
3 4hj 12
3 2vb 4
3 1ce 6


Trying to group by ID and show highest value, output below:

Id MaxOfvalue DESC
1 7 g56
2 9 6gh
3 12 4hj


easy enough without DESC -
SELECT Table1.Id, Max(Table1.value) AS MaxOfvalue
FROM Table1
GROUP BY Table1.Id;

but as soon as I add DESC on select line (even something desperate like
ucase(desc)) I get a message saying it's missing on the Group By line -
and as soon as I add it and run the query - I get all the records in
the table.

How do I overcome this please?

thank you.

 

[Marshall Barton]

hi, table as follows:


ID DESC VALUE
======================
1 g56 7
1 j78 6
1 n67 1
1 7hh 1
2 3df 3
2 9kk 3
2 6gh 9
3 4hj 12
3 2vb 4
3 1ce 6


Trying to group by ID and show highest value, output below:

Id MaxOfvalue DESC
1 7 g56
2 9 6gh
3 12 4hj


easy enough without DESC -
SELECT Table1.Id, Max(Table1.value) AS MaxOfvalue
FROM Table1
GROUP BY Table1.Id;

but as soon as I add DESC on select line (even something desperate like
ucase(desc)) I get a message saying it's missing on the Group By line -
and as soon as I add it and run the query - I get all the records in
the table.

That's what's supposed to happen. You need to use a
subquery in the WHERE clause when you wnat additional
fields:

SELECT ID, DESC, VALUE
FROM Table1
WHERE VALUE = (SELECT Max(T2.Value)
FROM Table1 As T2
WHERE T2.ID = Table1.ID)

Note that Value is a reserved word, so if you run into
trouble (and sooner or later you will), change the name of
that field to something that's not a real word.

 

[ironicbionic]

thank you for that - much appreciated. As you may have gathered I'm
very new to this, if I split my table into two - how should the query
look now? Tried and failed to ammend your code without success.

<table1>
ID DESC
=========
1 AAA
2 BBB
3 CCC

<table 2>
ID VALUE
=============
1 7
1 6
1 1
1 1
2 3
2 3
2 9
3 12
3 4
3 6


thanks again.

 

[Marshall Barton]

thank you for that - much appreciated. As you may have gathered I'm
very new to this, if I split my table into two - how should the query
look now? Tried and failed to ammend your code without success.

<table1>
ID DESC
=========
1 AAA
2 BBB
3 CCC

<table 2>
ID VALUE
=============
1 7
1 6
1 1
1 1
2 3
2 3
2 9
3 12
3 4
3 6

What kind of "ammending" did you do to what I suggested? I
thought I followed your example exactly.

This data is very different from what you posted before.
Before, an ID could have many different DESC values, but now
each ID can have only one DESC value.

SELECT table1.ID, table1.DESC,
Count(table2.Value) As CountOfValues
FROM table1 INNER JOIN table2
ON table1.ID = table2.ID
GROUP BY table1.ID, table1.DESC