Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

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
0
Ruttensoft
Asked:
Ruttensoft
  • 5
  • 4
1 Solution
 
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
 
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
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
 
SharathData 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

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

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now