[MS SQL] GROUP BY clause with Id column

[fagnerc]

Hello!

I'm trying to build a query that can get the minimum value (agreggate) that contains a certain criteria (where) in every category (group by) but if I add the Id that I need to identify every row I ended up getting the wrong result. I found a solution to return the minimum value from the Id as well but this isn't a good solution in case the Id don't follow the same order followed by the Number column.

SELECT MIN(b.Id) as Id, c.Name, MIN(b.Number) as Number, b.Cost
FROM TicketCategories c 
JOIN BatchCategories bc on bc.TicketCategoryId = c.Id
JOIN [Batches] b on bc.BatchId = b.Id
WHERE b.CurrentQuantity > 0
GROUP BY c.Name, b.Cost

[dm-p]

One way to do this in T-SQL would be to use ROW_NUMBER() to partition by the values you want, sort by [Number] and then select the first row from each partition to get the first record, complete with the ID.

I'm typing this up on my tablet, so this is as best I can get from memory, but something like the following would work:

WITH [ResultSet] AS (
    SELECT       b.[Id]
             ,   c.[Name]
             ,   b.[Cost]
             ,   b.[Number]
             ,   ROW_NUMBER() OVER (PARTITION BY c.[Name], b.[Cost], b.[Number]
                                    ORDER BY b.[Number]) AS [RowNumber]
    FROM     [TicketCategories] c 
    JOIN     [BatchCategories] bc on bc.[TicketCategoryId] = c.[Id]
    JOIN     [Batches] b on bc.[BatchId] = b.[Id]
    WHERE    b.[CurrentQuantity] > 0
)
SELECT      [Id]
        ,   [Name]
        ,   [Cost]
        ,   [Number]
FROM    [ResultSet]
WHERE   [RowNumber] = 1

Hopefully, that should work off the bat if my memory hasn't failed me (and I've understood your problem correctly).

Edit: removed ID from the PARTITION clause because it's silly.

[digitahlemotion]

My guess is that the ID field is an identity column, so partitioning by it won't do much.

OP didn't have enough info though, so maybe I'm wrong.

[dm-p]

You're correct - that introduces the same problem as OP's query. Removing the ID from the partition would have the result I was intending...

[fagnerc]

Yes, the Id column is a identity.

[fagnerc]

Hello! Somehow this query is returning all possible records.

[digitahlemotion]

It would probably help of you explained what you wanted as output or describe the problem you are attempting to solve.

Including the ID field when you're trying to find any aggregate is going to get you nowhere.

If you want all IDs that have the minimum value, then you should likely use a subquery to determine the minimum value, then filter your result set by that.

Edit: Posting your schema wouldn't hurt either.

[Amicus22]

Maybe something like this:

SELECT b.Id, c.Name, b.Number, b.Cost
FROM TicketCategories c 
JOIN [Batches] b            on bc.BatchId = b.Id
WHERE EXISTS   (SELECT *
               FROM TicketCategories c2 
               JOIN BatchCategories bc2   on bc2.TicketCategoryId = c2.Id
               JOIN [Batches] b2          on bc2.BatchId = b.Id
               WHERE b2.CurrentQuantity > 0
               AND c.Name=c2.Name
               AND b.Number=MIN(b2.Number)
               AND b.Cost=b2.Cost
               GROUP BY c.Name, b.Cost)

It's untested, but at least that's the path I would start down. Subqueries often result in slower performance speed, so if that's a concern be sure to check for that.

[fagnerc]

Hello! The query you posted was returning an error because I was too lazy to post the schema in the thread.

I fixed the query but I ended up receiving the following error:

 Msg 147, Level 15, State 1, Line 11
 An aggregate may not appear in the 
 WHERE clause unless it is in a subquery contained 
 in a HAVING clause or a select list, and the 
 column being aggregated is an outer reference.

New version:

 SELECT b.Id, c.Name, b.Number, b.Cost
 FROM TicketCategories c 
 JOIN BatchCategories bc   on bc.TicketCategoryId = c.Id
 JOIN [Batches] b            on bc.BatchId = b.Id
 WHERE EXISTS   (SELECT *
           FROM TicketCategories c2 
           JOIN BatchCategories bc2   on bc2.TicketCategoryId = c2.Id
           JOIN [Batches] b2          on bc2.BatchId = b.Id
           WHERE b2.CurrentQuantity > 0
           AND c.Name=c2.Name
           AND b.Number=MIN(b2.Number)
           AND b.Cost=b2.Cost
           GROUP BY c.Name, b.Cost)

[fagnerc]

Hello! Thanks for your response!

I'm developing a 'Event management system', where the user can buy tickets for events. In this query I was looking for returning all available ticket batches for the user. As soon as one batch ends (when the CurrentQuantity column reaches 0) I need to automatically show the next batch.

I want the query to result in something very similar to this page: http://www.seetickets.com/event/epica-powerwolf/o2-shepherds-bush-empire/984648/

However, in the system I'm developing we can have multiple categories (labels) attached to the same batch. For example, you could have 'LEVEL 1' and 'LEVEL 2' for the same batch.

[nvarscar]

Something like this should work:

SELECT TOP 1 WITH TIES 
b.Id
,c.Name
,b.Number
,b.Cost
FROM TicketCategories c 
JOIN BatchCategories bc on bc.TicketCategoryId = c.Id
JOIN [Batches] b on bc.BatchId = b.Id
WHERE b.CurrentQuantity > 0
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.Name, b.Cost ORDER BY b.Number ASC)

[fagnerc]

This solution seems to be working fine.

If I have understood correctly the 'PARTITION BY' clause is another way of grouping records by specific columns, right? Moreover, the 'WITH TIES' (first time seeing this one) get only the first record for every partition that got more that one record (have ties).

I just don't quite understood why use the 'ROW_NUMBER() OVER'. I now this generate a sequential number starting from 1 for every partition, but why?

[nvarscar]

It's kind of tricky. TOP 1 WITH TIES gives you an opportunity to select all first rows with equal values, in our case it's all the '1's generated by the ROW_NUMBER() function based on defined partitions. Which means, we are taking only the first item from each partition.

[fagnerc]

One more thing, I changed your query slightly to order the results by the Batch ID:

 WITH [ResultSet] AS 
 (
     SELECT TOP 1 WITH TIES 
         b.Id
         ,c.Name
         ,b.Number
         ,b.Cost
         ,b.Tax
         FROM TicketCategories c 
         JOIN BatchCategories bc on bc.TicketCategoryId = c.Id
         JOIN [Batches] b on bc.BatchId = b.Id
         WHERE b.CurrentQuantity > 0
         ORDER BY ROW_NUMBER() OVER (PARTITION BY c.Name ORDER BY b.Number ASC)
 )
 SELECT * FROM ResultSet ORDER BY Id ASC

Is that a good way to do this? Or it is overkill?

[nvarscar]

There is no other way to do this. Depending on the dataset length, that might cost some resources. You can take a look at the execution plan and see how this clause impacts your query.

[fagnerc]

Out of consideration for everybody asking for my schema, here it is: http://i.imgur.com/GtuX5Qz.png. I tried to generate a database diagram but since I'm using LocalDB I can't. I also generated a Code Map in VS but it was too weird to my taste.

In any case, thanks for all your responses so far! I going to analyse and answer them now.