www.webdeveloper.com
Results 1 to 4 of 4

Thread: Simple SQL Problem

  1. #1
    Join Date
    Feb 2007
    Posts
    3

    Simple SQL Problem

    Hey guys

    Sorry to charge in and ask a question with my first post, but I'm extremely puzzled by some SQL and really in need of some help.

    The table set up is very simple - I have just one relevant table called Gift. It has three relevant fields - giftID, giftDate and constituentID. I need to find the earliest giftID for each constituentID. Simple, ya?

    So if the data was

    giftID...giftDate...constituentID
    3..........1/1/2006..........2
    1..........1/1/2005..........2
    5..........2/2/2006..........3
    5..........2/2/2006..........2
    8..........8/8/2006..........3

    It should return

    giftID....constituentID
    1...........2
    5...........3

    I can't do this though without using several very slow subqueries which I'm finding quite annoying, because I'm sure it should be possible in one simple SQL statement.

    Any help would be gratefully received! Any ideas?

    Cheers
    Iain
    Last edited by IainT; 02-14-2007 at 09:54 AM.

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    What dbms are u using? this will work for most.
    Code:
    SELECT	t.giftID, t.constituentID
    FROM	tableName t
    JOIN	(
    	SELECT	constituentID, min(giftDate) dt
    	GROUP BY
    		constituentID
    ) x
    On	x.constituentID = t.constituentID
    And	x.dt = t.giftDate

  3. #3
    Join Date
    Feb 2007
    Posts
    3
    Cheers Russell. Not too different, but still much more efficient apparently than the SQL I was using.

    Much appreciated.
    I

  4. #4
    Join Date
    Feb 2007
    Posts
    3
    Cheers Russell. Not that different to the SQL I had been using, but apparently much more efficient.

    Much appreciated!
    I

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles