Hello, I need help with a SELECT

[Lftsk]

Hello,

I need help with a SELECT Statement.

I have a table with 4 columns.

Column 1 is RecType ("A", "B", "C", "D&quot
Column 2 is Col1
Column 3 is Col2
Column 4 is Col3

All I need is one record per RecType. Each record is the highest value in Col3 for each RecType.

Thank you.

 

[Kurniadi]

Hi Lftsk...

I don't understand your means.
Can you tell me with output that you want ?

Thank's
Kurniadi

 

[Lftsk]

Sorry I could have been more clear.

Let's say I have 8 rows in the table.

Row RecType Col1 Col2 Col3

1 A 100 200 300
2 A 100 200 400
3 B 100 200 300
4 B 100 200 500
5 C 100 200 300
6 C 100 200 600
7 D 100 200 300
8 D 100 200 700

What I'm looking for is the Individual row that has the highest value in Col3 for each RecType.

In the example above I would want to retrieve rows 2,4,6 and 8 since (e.g.) row 2's Col3 value is the highest within all rows for RecType A. Hope that's clear. Once again any help would be greatly appreciated!

 

[dianal]

SELECT Row, RecType, Max(Col3)
FROM tblMy
GROUP BY RecType;

 

[ClaireHCM]

dianal,
I tried your query ,it's sowing this error msg.

Column 'tblMy.Row' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

 

[ClaireHCM]

if you dont need to retrive row values.You can try this query

select rectype,max(col1),max(col2),max(col3) from table1 group by rectype

or
select rectype,min(col1),min(col2),max(col3) from table1 group by rectype

because all the values in col2 and col1 are the same.

But I havent thought a way how to get values of row

 

[dianal]

Yep, my mistake - last minute change
SELECT Row, RecType, Max(Col3)
FROM tblMy
GROUP BY Row, RecType;
If you do not need row,you can saely delete from select and from group by.

 

[ClaireHCM]

But this will turn out all the values.....
Because you are grouping also by row.But rows are distinct for every row.

The result will be like this

row RecType
----------- ------- -----------
1 A 300
2 A 500
3 B 300
4 B 600
5 C 300
6 C 600
7 D 300
8 D 700
9 A 600

 

[dianal]

Correct.

SELECT RecType, Max(Col3)
FROM tblMy
GROUP BY RecType;

If you need also Row, try this:

SELECT Last(Row), RecType, Max(Col3)
FROM tblMy
GROUP BY RecType;

 

[dianal]

Well, I checked, but Last() is not the correct way of getting the row.

 

[Kurniadi]

Hi Lftsk,

Your output may be like this :

RecType col1 col2 col3
------- ------- -------- --------
A 100 200 400
B 100 200 500
C 100 200 600
D 100 200 700

You can try to select your data,

SELECT RecType, MAX(col1) AS col1, MAX(col2) AS col2, MAX(col3) AS col3 FROM Table1
GROUP BY RecType

or like this,

SELECT DISTINCT RecType, col1, col2,
col3 = (SELECT MAX(a.col3) FROM table1 a WHERE a.RecType = Table1.RecType) FROM Table1

Please try it and don't forget read The SQL Server Books On Line with the topic SELECT.

Thank's
Kurniadi