Solved

Selecting the MIN value from several columns

Posted on 2008-10-29
5
322 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select - identify record discrepancies 1 31
Many to one in one row 2 39
Need help with a query 14 37
Access #Deleted data 20 41
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

756 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