?
Solved

Selecting the MIN value from several columns

Posted on 2008-10-29
5
Medium Priority
?
325 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 2000 total points
ID: 22844846
Are you still there? Works good doesn't it arccfaith.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

764 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