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

Answers


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;

See SQL Fiddle with Demo


Need Your Help

Convert Long to DateTime in Windows Phone C#

c# datetime windows-phone-8

I am getting requests from API for my app. In that the date/time is claimed to be in the format of Long. I am trying to convert it using the following lines.

Creating a gallery app for Android

java android client-server

I want to create an app that takes pictures from an online gallery and displays them fullscreen in a flick-left/flick-right fashion. The thing is that if the online gallery is updated I want the pi...