maybe is a stupid question.. about CASE

I have a little query in access ..now i must write it in T-Sql

the original...
(MSG.IDLINGUA = IIF( EXISTS
(SELECT * FROM ESMESSAGGI AS MSG WHERE MSG.IDLINGUA = [@.idLingua] AND MSG.IDMESSAGGIO = BITS.IDDESCR),
[@.idLingua], LDEF.IDLINGUA))

I can't be able to assign a select value at my field... IDLINGUA to perform another select..
Of course I have used the "select case when exists"

Anyone could be help me ?Post the code you tried that failed.|||By the reference on your where clause to a table or alias called "Bits", it seems this is part of a larger query.
Please, post your whole query.

Max Value in Column Other than Return Value Column

Hi,
This may be a very simple query but I couldn't figure out how to do it. I
have a table with two columns, COL1 and COL2. I need to write a query that
will return the value of COL1 from the row where COL2 has the greatest value
.
For example,
COL1 COL2
-- --
A 2
B 3
C 1
The query should return "B".
I don't know if this makes a difference, but this query will actually be a
subquery in the select clause of a main query.
Thanks!
JohnSELECT COL1
FROM myTable
WHERE COL2=(SELECT MAX(COL2) FROM myTable)
Note that if COL2 is not unique you may get multiple
rows back|||Here is one way to do it:
declare @.tbl table
(
col1 varchar (10)
, col2 int
)
insert into @.tbl
values ('A',1)
insert into @.tbl
values ('B',3)
insert into @.tbl
values ('C',2)
-- Actual Query
select a.Col1
from @.tbl a
inner join (select MAX(col2) col2
from @.tbl
) b
on a.col2 = b.col2
"John Walker" wrote:

> Hi,
> This may be a very simple query but I couldn't figure out how to do it. I
> have a table with two columns, COL1 and COL2. I need to write a query tha
t
> will return the value of COL1 from the row where COL2 has the greatest val
ue.
> For example,
> COL1 COL2
> -- --
> A 2
> B 3
> C 1
> The query should return "B".
> I don't know if this makes a difference, but this query will actually be a
> subquery in the select clause of a main query.
> Thanks!
> John
>|||Ok, i see. You have to use a subquery to first get that Max value then use
that to filter. Yeah that makes sense.
Thanks,
John
"John Walker" wrote:

> Hi,
> This may be a very simple query but I couldn't figure out how to do it. I
> have a table with two columns, COL1 and COL2. I need to write a query tha
t
> will return the value of COL1 from the row where COL2 has the greatest val
ue.
> For example,
> COL1 COL2
> -- --
> A 2
> B 3
> C 1
> The query should return "B".
> I don't know if this makes a difference, but this query will actually be a
> subquery in the select clause of a main query.
> Thanks!
> John
>|||Or you could use TOP:
SELECT TOP 1 col1
FROM T1
ORDER BY col2 DESC;
Note though, that if col2 is not unique, this query is non-deterministic
(more than one possible "correct" result), and will still return a single
row.
Unlike the following query which can potentially return multiple rows:
SELECT col1
FROM T1
WHERE col2 =
(SELECT MAX(col2)
FROM T1);
If col2 is non-unique, and you're still after a deterministic result with a
single row, you need to introduce a tiebreaker. For example, you can use the
primary key:
SELECT TOP 1 col1
FROM T1
ORDER BY col2 DESC, pk DESC;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"John Walker" <JohnWalker@.discussions.microsoft.com> wrote in message
news:AFF206D6-74E3-482E-B095-D128F2F90A1A@.microsoft.com...
> Ok, i see. You have to use a subquery to first get that Max value then
> use
> that to filter. Yeah that makes sense.
> Thanks,
> John
> "John Walker" wrote:
>