Solved

Selecting the MIN value from several columns

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In T-SQL cursor convert smallint to varchar 15 50
T-SQL: Only Wanting One Record 8 59
sql server connection string in config file 4 39
sql trace 4 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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…

737 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