distinct query

Hi
I have a query as below:
select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
[T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
Explanation
[T_Giftcards].[M_Cardnumber] is a unique number,
[transaction].[M_GIFTCARDNO] is the matching filed in another table but this
transaction table has may hundreds of transactions per T_Giftcard number, Ho
w
can I just display 1 T_Giftcards number and one matching transaction'
Help much appreciatedIvo (Ivo@.discussions.microsoft.com) writes:
> I have a query as below:
> select [transaction].[M_GIFTCARDNO], [T_GIFTCARDS].[M_SERIALNO],
> [T_GIFTCARDS].[M_CARDNUMBER], [transaction].[req_login_time]
> from [T_GIFTCARDS], [transaction]
> where req_login_time between '5 October,2005' and '6 December, 2005'
> and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
> Explanation
> [T_Giftcards].[M_Cardnumber] is a unique number,
> [transaction].[M_GIFTCARDNO] is the matching filed in another table but
> this transaction table has may hundreds of transactions per T_Giftcard
> number, How can I just display 1 T_Giftcards number and one matching
> transaction'
select [T_GIFTCARDS].[M_SERIALNO], [T_GIFTCARDS].[M_CARDNUMBER],
MIN([transaction].[req_login_time])
from [T_GIFTCARDS], [transaction]
where req_login_time between '5 October,2005' and '6 December, 2005'
and [T_GIFTCARDS].[M_CARDNUMBER]=[transaction].[M_GIFTCARDNO]
GRUOP BY [T_GIFTCARDS].[M_CARDNUMBER], [T_GIFTCARDS].[M_SERIALNO]
Or use MAX() if you think that is better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql