Solved

Selecting the MIN value from several columns

Posted on 2008-10-29
5
324 Views
Last Modified: 2008-11-03
Hi,

I'm trying to select the minimum value found across several columns.  If you look at the code snippet, what I would like is a select statement that will display one min date for each case_id.  For example, case_id 1 should select the date from CODE3, 2 should display the date from CODE2 and 3 should display the date from CODE1.

Any suggestions appreciated
create table #tmpMaxEvents
(
	case_id NUMERIC(9,0),
	CODE1 DATETIME,
	CODE2 DATETIME,
	CODE3 DATETIME
)
 
INSERT INTO #tmpMaxEvents
VALUES (1,'2008-10-26 19:37:35.013','2008-10-29 19:37:35.013','2008-10-22 19:37:35.013')
 
INSERT INTO #tmpMaxEvents
VALUES (2,'2008-10-25 19:37:35.013','2008-10-24 19:37:35.013','2008-10-28 19:37:35.013')
 
INSERT INTO #tmpMaxEvents
VALUES (3,'2008-10-21 19:37:35.013','2008-10-25 19:37:35.013','2008-10-26 19:37:35.013')
 
SELECT * FROM #tmpMaxEvents
 
DROP TABLE #tmpMaxEvents

Open in new window

0
Comment
Question by:arccfaith
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22835435
select case_id, case when Code1 > code2 then Code2
when Code2 > Code3 then Code3 else code1 end as MINDATE from #tmpMaxEvents
0
 
LVL 1

Author Comment

by:arccfaith
ID: 22835616
Many thanks but unfortunately in that code if Code1 is the lowest value then code2 is the highest and code3 in the middle, it doesn't select code 1.  I've already gone down this road though.  I though there might be a SQL function that could be used.
create table #tmpMaxEvents
(
	case_id NUMERIC(9,0),
	CODE1 DATETIME,
	CODE2 DATETIME,
	CODE3 DATETIME
)
 
INSERT INTO #tmpMaxEvents
VALUES (1,'2008-10-26 19:37:35.013','2008-10-29 19:37:35.013','2008-10-28 19:37:35.013')
 
INSERT INTO #tmpMaxEvents
VALUES (2,'2008-10-25 19:37:35.013','2008-10-29 19:37:35.013','2008-10-28 19:37:35.013')
 
INSERT INTO #tmpMaxEvents
VALUES (3,'2008-10-21 19:37:35.013','2008-10-27 19:37:35.013','2008-10-26 19:37:35.013')
 
 
SELECT * FROM #tmpMaxEvents
 
select case_id, 
	case 
		when Code1 > code2 then Code2
		when Code2 > Code3 then Code3 
		else code1 
	end as MINDATE from #tmpMaxEvents
 
DROP TABLE #tmpMaxEvents

Open in new window

0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22835831
Well there is a way to join itself then use min function give me a minute and I'll post it
0
 
LVL 11

Expert Comment

by:MacNuttin
ID: 22835916
select case_id, min(ct.code1)
     from (select case_id, code1
       from #tmpMaxEvents union ( select case_id, code2
       from #tmpMaxEvents) union ( select case_id, code3
       from #tmpMaxEvents))ct group by case_id
0
 
LVL 11

Accepted Solution

by:
MacNuttin earned 500 total points
ID: 22844846
Are you still there? Works good doesn't it arccfaith.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question