sql server programming: I know I can't select fields unless they're included in an aggregate
or a group by, but that's the closest I can come up with to explain
what I need.

I've got the following table ("T"):

AID FID VAL
500 910 x
520 910 y
520 950 z
500 950 a
500 990 b
520 990 c

I want one VAL for each distinct FID, as ordered by AID DESC. Or
another way to say it is I want the VAL from the first record of each
grouping of like FID, as ordered by AID DESC. So the result I'm
looking for is (parenthesis are for unneeded data, but indicative of
the row I'm hoping to have selected):

y 910 (520)
z 950 (520)
c 990 (520)

SELECT VAL, FID, MAX(AID) FROM T GROUP BY FID
....sort of conveys what I want, though obviously not to SQL Server.
:)

Sorry to bug y'all with what seems like such a simple query, but it's
been vexing me for hours, and I haven't been able to come up with an
answer after extensive searching through newsgroups and websites
(usually I find these kind of answers pretty easily). If anyone can
steer me to a query that would yield the results i'm looking for, I'd

SELECT val, fid, aid
FROM SomeTable AS T
WHERE aid =
(SELECT MAX(aid)
FROM SomeTable
WHERE val = T.val
AND fid = T.fid)

--
David Portas
SQL Server MVP
--

Try,


select distinct
fid,
val
from
tableA
where
aid = (select max(b.aid) from tableA b)



AMB

Masa and Portas gave you answers, but your real problem is that you
"just don't get it" yet. Look at the non-relational words your used in
your spec! Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.

You describe a procedure for getting a record in a sequential file; what
you wanted was a predicate to pick a row.

[quoted text, click to view]
another way to say it is I want the VAL from the first record [sic] of
each grouping of like FID, as ordered by AID DESC. <<

Try: I want the subset of rows from table T which have the maximum value
of SID within each FID grouping.

Since you did not bother with any DDL, we have no idea if that is a
reasonable spec or not (see note at bottom of postings) from the keys
and constraints. But then the answers given just drop out of the spec
sentence.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

That doesn't seem to work for me. I ran the above query and it
resulted in all 6 rows being returned (from my sample table as

Thanks to all for your replies! Mr. Celko's advice to use DDL will be
adhered to in any future posts of mine, I greatly appreciate the education
on that point. Mr. Mesa's query got me the results I need, many thanks. And
apologies to Mr. Portas for providing an inadequate question from which to
work off of, I appreciate the effort you put forth into answering my
question. -Jason

I believe that is correct, and the 2nd query you posted works and makes
sense to me. Thanks again! -Jason

Sorry Jason. Try this:

SELECT val, fid, aid
FROM SomeTable AS T
WHERE aid =
(SELECT MAX(aid)
FROM SomeTable
WHERE fid = T.fid)

--
David Portas
SQL Server MVP
--

If I've understood your requirement correctly you need to add

WHERE fid = T.fid

to Alejandro's subquery, although it will give the same result as my version
for the sample data given.

--
David Portas
SQL Server MVP
--