Solved

Selecting the MIN value from several columns

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Add total to a tablix with ReportItem values 15 42
Sql Join Problem 2 33
Help Extract Specific in SQL 8 27
Need SQL that flips  value 5 21
I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now