Grouping in SQL Statement

I have the following SQL statement:

    SELECT TOP 30
    a.ClassAdID,    -- 0
    a.AdTitle,      -- 1
    a.ClassAdCatID, -- 2
    b.ClassAdCat,   -- 3
    a.Img1,         -- 4
    e.Domain,       -- 5
    a.AdText,       -- 6
    a.RegionID,     -- 7
    a.IsEvent,      -- 8
    a.IsCoupon,     -- 9
    b.ParentID,     -- 10
    a.MemberID,     -- 11
    a.AdURL,        -- 12
    a.Location,     -- 13
    a.GroupID   开发者_如何学Python    -- 14
    FROM ClassAd a
    INNER JOIN ClassAdCat b ON b.ClassAdCatID = a.ClassAdCatID
    INNER JOIN Member d ON d.MemberID = a.MemberID
    INNER JOIN Region e ON e.RegionID = a.RegionID
    WHERE DATEDIFF(d, GETDATE(), a.ExpirationDate) >= 0
    AND PostType <> 'CPN'
    ORDER BY a.CreateDate DESC

I want to only show one from each GROUPID... How can I adjust the statement to achieve this as I am lost with DISTINCT, GROUP BY etc..

Any help would be appreciated.

Many thanks,

Paul

---

You can use ROW_NUMBER function to partition data set based on GroupId values thus: for every new GroupId values the counter is restarted from 1 and the first row (with ROW_NUMBER = 1) is the newest record (a.CreateDate DESC). Then, we filter all records having ROW_NUMBER = 1 .

SELECT TOP 30 *
FROM
(
    SELECT
    a.ClassAdID,    -- 0
    a.AdTitle,      -- 1
    a.ClassAdCatID, -- 2
    b.ClassAdCat,   -- 3
    a.Img1,         -- 4
    e.Domain,       -- 5
    a.AdText,       -- 6
    a.RegionID,     -- 7
    a.IsEvent,      -- 8
    a.IsCoupon,     -- 9
    b.ParentID,     -- 10
    a.MemberID,     -- 11
    a.AdURL,        -- 12
    a.Location,     -- 13
    a.GroupID,      -- 14
    ROW_NUMBER() OVER(PARTITION BY a.GroupId ORDER BY a.CreateDate DESC) AS PseudoId
    FROM ClassAd a
    INNER JOIN ClassAdCat b ON b.ClassAdCatID = a.ClassAdCatID
    INNER JOIN Member d ON d.MemberID = a.MemberID
    INNER JOIN Region e ON e.RegionID = a.RegionID
    WHERE DATEDIFF(d, GETDATE(), a.ExpirationDate) >= 0
    AND PostType <> 'CPN'
) q
WHERE q.PseudoId = 1;

---

GROUP BY goes with an AGGREGATE function... meaning you want to add up the values in the group, or find the biggest, or smallest in the group etc.

DISTINCT will remove duplicate rows.

in your query, you may be getting a bunch of not-so-similar rows that all happen to have the same group_id... if this is so, then you need to decide which one of those rows you really want to see.

maybe you want the newest one, or the one with the longest name, or something like that.

for grouping, you would pick a column like createdon and say something like MAX( createdon ) in the select list, then group on every other column in the select list to find the rows that match each other (except for created on), and return that only once with the largest value for created on... hope that makes sense.

edit:

very simple example for group id and create date. ( you can keep adding more columns as needed - one in the group by list for every one in the select list :

SELECT groupid, max( createdate )
FROM ClassAd
GROUP BY groupId

---

If I understand correctly you want to get one row from each group (like groupid)

I used sql server 2005 (Nothwind)

SELECT   TOP 30  Customers.CompanyName, Orders.ShipCity, Orders.Freight
FROM         Customers INNER JOIN
                      Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CompanyName, Orders.ShipCity, Orders.Freight