?
Solved

Help with Query using GREATEST

Posted on 2014-08-04
6
Medium Priority
?
164 Views
Last Modified: 2014-08-04
Hi.
I have data that looks like this:  I need to return the GREATEST date (most current) along with the USER associated with the date. So I want to return 2 fields   DATE & USER

FIELD1     USER1           FIELD2         USER2           FIELD3         USER3
1/1/2001    BOB           1/4/2013       JANE            8/4/2014       MIKE

Should return
8/4/2014       Mike
0
Comment
Question by:JElster
  • 3
  • 2
6 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40238895
SELECT GREATEST(field1,fiedl2, field3, field4) from yourtable
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40238896
put semi colon after:

SELECT GREATEST(field1,fiedl2, field3, field4) from yourtable;

Open in new window

0
 
LVL 1

Author Comment

by:JElster
ID: 40238917
I need the user associated with the Greatest value
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40238963
Try this and see if it solves your problem.

SELECT t1.user1,t1.user2,t1.user3,t1.user4
FROM yourtable t1
INNER JOIN
(
    SELECT GREATEST(field1,field2,fiel3,fiel4)
    FROM yourtable t2
) t2 ON t1.ID=t2.ID

Open in new window



yourtable t1 and t2 are same table.
0
 
LVL 1

Author Comment

by:JElster
ID: 40239008
Sorry,. I need the  GREATEST VALUE and USER
So it returns

8/4/2014       Mike


thx
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 2000 total points
ID: 40239016
Select GREATEST (Field1, field2, Field3) as GreatestDate,
CASE WHEN field1 = GREATEST (Field1, field2, Field3) then User1
WHEN field2 = GREATEST (Field1, field2, Field3) then User2
WHEN field3 = GREATEST (Field1, field2, Field3) then User3
END as GreatestUser
From MyTable;
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 6 hours left to enroll

809 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