asked on

SQL Question

Hello

I have some table like this:

A B
1 1
1 3
1 2
2 2
2 1
2 3

Now I want only these rows, where b is the highest value for A

So i want:

1 3
2 3

But it's not always 3 the highest value

How can I do that?

Thanks

Guy Hengel [angelIII / a3]

this will do:

select t.*
  from yourtable t
 where t.b = ( select max(i.b) from yourtable i where i.a= t.a)

Open in new window

Sharath S

try this.

SELECT A,MAX(B) AS B
  FROM YourTable
 GROUP BY A

Open in new window

Ruttensoft

ASKER

Hm, so mytable is tbl_back_checkliste_aktiv, a = tbl_back_checkliste_aktiv_checkliste_typid, b = tbl_back_checkliste_aktiv_checkliste_version gives me this, right:

select t.*
from tbl_back_checkliste_aktiv t
where t.tbl_back_checkliste_aktiv_checkliste_version = (select max(i.tbl_back_checkliste_aktiv_checkliste_version) from tbl_back_checkliste_aktiv i where i.tbl_back_checkliste_aktiv_checkliste_typid= t.tbl_back_checkliste_aktiv_checkliste_typid)

But the query says:

There was an error parsing the query. [ Token line number = 3,Token line offset = 59,Token in error = select ]

What have I done wrong?

Ruttensoft

ASKER

Thanks Sarath, that did it!

Ruttensoft

ASKER

Ups, another thing: I have other columns in the table, so column d, e and f.

How can I add them to this query?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ruttensoft

ASKER

Boooooaa nice, thanks!!

Sharath S

I also updated your original query :)

SELECT T1.*
  FROM tbl_back_checkliste_aktiv T1
 INNER JOIN (SELECT tbl_back_checkliste_aktiv_checkliste_typid,
                    MAX(tbl_back_checkliste_aktiv_checkliste_version) AS tbl_back_checkliste_aktiv_checkliste_version
               FROM tbl_back_checkliste_aktiv
              GROUP BY tbl_back_checkliste_aktiv_checkliste_typid) AS T2
    ON T1.tbl_back_checkliste_aktiv_checkliste_typid = T2.tbl_back_checkliste_aktiv_checkliste_typid 
   AND T1.tbl_back_checkliste_aktiv_checkliste_version = T2.tbl_back_checkliste_aktiv_checkliste_version

Open in new window

Ruttensoft

ASKER

Thanks a lot!

Sharath S

you are welcome