Solved

help wih SQL

Posted on 2014-01-17
6
271 Views
Last Modified: 2014-01-17
I have a table with a date column (due_date).  I want to select the record with the max date, and return that date and the primary key  (ID).   how can I do this?
0
Comment
Question by:HLRosenberger
  • 4
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 39789470
Couple of ways to pull this off, this looks decent..
SELECT id, due_date
FROM your_table
WHERE due_date IN (SELECT max_date FROM your_table) 

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 39789500
SELECT TOP 1 id, due_date
FROM your_table
ORDER BY due_date DESC
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 39789504
See below.   I left out part of my question.  These records say are tasks associated with a job table.   This query picks off the max date out for all the records.  I'm actually creating a view, so I need a set of records that show the task ID and task Max(DueDate) for each job.

How could I modify this to get what I need?


 SELECT id, duedate
  FROM [tdsQA].[dbo].[tds_grant_funding_request_report]
  WHERE  duedate IN (SELECT max(duedate) FROM [tds_grant_funding_request_report])
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 1

Author Comment

by:HLRosenberger
ID: 39789515
This will be a view, so order by does not help.
0
 
LVL 1

Author Comment

by:HLRosenberger
ID: 39789535
Ah, think I got it:

  SELECT id, duedate
  FROM [tdsQA].[dbo].[tds_grant_funding_request_report]
  WHERE  duedate IN
   (SELECT max(duedate) FROM [tds_grant_funding_request_report] group by fk_funding_requestID)

where fk_funding_requestID is the "job" primary key, in effect.
0
 
LVL 1

Author Closing Comment

by:HLRosenberger
ID: 39789565
Thaanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

763 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