Subquery for min(field value)

Question

I have a query that will look for any 'Open_Tollgates' that are at a status '6' (not completed) and also the 'Department' responsible.

I ONLY want to retrieve the min(pgAct.Gate_ID) (the first Gate_Number that is not complete).

Problem is, each Tollgate (i.e. '706') is distinct because it has a specific activity ID (not displayed).

I know I need to do a subquery to filter the data. As seen below, I would essentially need just the first line (706 3 Sourcing).

What would you recommend?

Before subquery:

select pgAct.ID as Open_Tollgate, pgAct.Gate_ID as Gate_Number, secGroup.[Group] as Department
from [dbo].[Project_Gate_Activities] pgAct
join [dbo].[Activities] as Act
on pgAct.activity_order = Act.order_id
join [dbo].[Security_Group] as secGroup
on Act.group_id = secGroup.group_id
where pgAct.ID = 706 
and status_id = 6
group by pgAct.id, pgAct.gate_id, secGroup.[Group]

Before subquery result set:

706 3 Sourcing

706 4 Accounting

706 4 Finance

706 4 Logistics

706 5 IT

706 6 Accounting

706 6 Finance

706 6 Purchasing

706 7 IT

706 7 Sales

706 8 Accounting

Look at what you posted and ask yourself if you would be able to answer this question based on what you posted. We have no knowledge of your project, table structures, data or requirements. We kind of need ALL of those to help. Try looking here. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 03 '16 at 18:38
You want the MIN gate_ID, so why are you grouping by GateID? Can't you just remove it from the GROUP BY and put MIN on it in the SELECT list? — Tab Alleman, May 03 '16 at 18:38
Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — Tab Alleman, May 03 '16 at 19:08

JamieD77 · Answer 1 · 2016-05-03T18:47:33.430

You can try using ROW_NUMBER.. you need to use a subquery or cte like this.

;WITH cte AS 
    (SELECT  pgAct.ID AS Open_Tollgate,
            pgAct.Gate_ID AS Gate_Number,
            secGroup.[Group] AS Department,
            ROW_NUMBER() OVER (PARTITION BY pgAct.ID ORDER BY pgAct.Gate_ID) Rn
    FROM    [dbo].[Project_Gate_Activities] pgAct
            JOIN [dbo].[Activities] AS Act ON pgAct.activity_order = Act.order_id
            JOIN [dbo].[Security_Group] AS secGroup ON Act.group_id = secGroup.group_id
    WHERE   pgAct.ID = 706
            AND status_id = 6
    GROUP BY pgAct.id,
            pgAct.gate_id,
            secGroup.[Group]
    )
SELECT * FROM cte WHERE Rn = 1

if you're only select one pgAct.ID at a time.. you can also use SELECT TOP 1, like so

SELECT TOP 1 
        pgAct.ID AS Open_Tollgate,
        pgAct.Gate_ID AS Gate_Number,
        secGroup.[Group] AS Department,
FROM    [dbo].[Project_Gate_Activities] pgAct
        JOIN [dbo].[Activities] AS Act ON pgAct.activity_order = Act.order_id
        JOIN [dbo].[Security_Group] AS secGroup ON Act.group_id = secGroup.group_id
WHERE   pgAct.ID = 706
        AND status_id = 6
ORDER BY pgAct.ID, 
        pgAct.Gate_ID

Subquery for min(field value)

1 Answers1