Simplify a SQL query

Posted on 2011-05-09
Last Modified: 2012-05-11
I am currently using 2 Queries to get to the value i am looking for and  am wondering if i could do this in one.

My table has 2 Col's of intrest lets called them ColA and ColB

ColA        ColB
10001        50
10008        50
10014        50
10002        51
10009        51
10015        51
10003        52
10009        52

and so on, i have the value of ColA lets say 10002 and i need to find the largest value
in my table for ColA where ColB = the same as for the row of 10002 which is 51 in the
sample case.

Right now i query for Colb based on 10002 and then in a second query i select max of
colA where colB = the value i got infirst query.

Any ideas to do this in one query would be great
Question by:AlexPonnath
    LVL 33

    Expert Comment

    Please show us the query you are using now.
    LVL 12

    Expert Comment

    Is this what you're doing now:
    select ColA,ColB from myTable where ColB=(select ColB from myTable where ColA=1002)?
    LVL 33

    Accepted Solution

    select max(ColA)
    from myTable
    where ColB = (select top 1 ColB from myTable where ColA = 10002 )
    LVL 18

    Assisted Solution

    Select max(ColA)  from YourTable
    where ColB =
    (Select ColB from  YourTable where ColA= 10002)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    A short article about a problem I had getting the GPS LocationListener working.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now