>Hi,
>
>I’ve got quite a tricky SQL query (well, at least for me it is, I’m
hoping someone may have done this before)
>
>Here’s a simplified example of what I’m trying to do:
>
>I have a table with
>
>ID GroupID FKCode Value
>1 1 ABC +5
>2 1 XYZ -5
>3 2 ABC +8
>4 2 XYZ -8
>5 3 ABC -2
>6 3 XYZ +2
>
>I’d like to select the first record of each group, eg.
>
>ID GroupID FKCode Value
>1 1 ABC +5
>3 2 ABC +8
>5 3 ABC -2
>
>Any ideas?
Very simple:
select ID, GroupID, FKCode, Value
from MyTable T
where not exists(select * from MyTable T2
where t.GroupID = t2.GroupID
and t.ID > t2.ID)
If ID is the primary key and you have an index for GroupID, I would
expect this to be quite fast. Sean's solution is an alternative that
maybe have similar performance, the other suggestions ought to be
avoided except for tiny tables.
HTH,
Set
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
[email protected]
[email protected]
<*> To unsubscribe from this group, send an email to:
[email protected]
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
