Solved

SQL Question

Posted on 2009-03-31
10
182 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
  • 5
  • 4
10 Comments
 
LVL 142

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 40

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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 40

Accepted Solution

by:
Sharath earned 500 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 40

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 40

Expert Comment

by:Sharath
ID: 24027009
you are welcome
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query help 2 44
subtract 1 in Access 2003 query 7 37
SQL Query resolving a string conversion issue 26 37
how to install/upgrade the Blitz responder kit 8 22
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

910 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now