Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Question

Posted on 2009-03-31
10
Medium Priority
?
187 Views
Last Modified: 2012-05-06
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
Comment
Question by:Ruttensoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24026837
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24026871
try this.
SELECT A,MAX(B) AS B
  FROM YourTable
 GROUP BY A

Open in new window

0
 

Author Comment

by:Ruttensoft
ID: 24026896
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
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

by:Ruttensoft
ID: 24026901
Thanks Sarath, that did it!
0
 

Author Comment

by:Ruttensoft
ID: 24026918
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24026969

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
 

Author Comment

by:Ruttensoft
ID: 24026981
Boooooaa nice, thanks!!
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24026996
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
 

Author Comment

by:Ruttensoft
ID: 24027000
Thanks a lot!
0
 
LVL 41

Expert Comment

by:Sharath
ID: 24027009
you are welcome
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question