Solved

# Simplify a SQL query

Posted on 2011-05-09
218 Views
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
0
Question by:AlexPonnath

LVL 33

Expert Comment

Please show us the query you are using now.
0

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)?
0

LVL 33

Accepted Solution

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

LVL 18

Assisted Solution

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

## Featured Post

### Suggested Solutions

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…