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
RuttensoftAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
SharathConnect With a Mentor Data EngineerCommented:

SELECT T1.*
  FROM YourTable T1
 INNER JOIN (SELECT A,MAX(B) AS B FROM YourTable GROUP BY A) AS T2
    ON T1.A = T2.A AND T1.B = T2.B

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
SharathData EngineerCommented:
try this.
SELECT A,MAX(B) AS B
  FROM YourTable
 GROUP BY A

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
RuttensoftAuthor Commented:
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?
0
 
RuttensoftAuthor Commented:
Thanks Sarath, that did it!
0
 
RuttensoftAuthor Commented:
Ups, another thing: I have other columns in the table, so column d, e and f.

How can I add them to this query?
0
 
RuttensoftAuthor Commented:
Boooooaa nice, thanks!!
0
 
SharathData EngineerCommented:
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

0
 
RuttensoftAuthor Commented:
Thanks a lot!
0
 
SharathData EngineerCommented:
you are welcome
0
All Courses

From novice to tech pro — start learning today.