Returning Minimum ID in Management Studio
Assume the following:
CREATE TABLE #Testing ( [id] [int] IDENTITY(1,1) NOT NULL, [store_id] [varchar](3) NULL, [sku] [varchar](14) NULL, [qty] [bigint] NULL, [http_action] [varchar](20) NULL ) INSERT INTO #Testing (store_id, sku, qty, http_action) SELECT '001','123456',1,'POST' UNION ALL SELECT '002','123456',1,'POST' UNION ALL SELECT '002','123456',1,'DELETE' UNION ALL SELECT '001','223456',5,'DELETE'
What’s a single query that you could run to return the minimum [id] for each [http_action]? Something like:
MIN_DELETE MIN_POST 3 1 GO
You can use an aggregate function with a CASE expression to get the min(id) for each http_action:
select min(case when http_action='DELETE' then id end) Min_Delete, min(case when http_action='POST' then id end) Min_Post from #testing;
See SQL Fiddle with Demo.
The above query places the min(id) values into separate columns, if you want them in rows, then you could use:
select min(id) id, http_action from #testing group by http_action;