Select *, for the most recent date

Select *, for the most recent date

Post by Michael Rame » Fri, 11 Jan 2002 04:56:16



I have a table, with a date field, and many other fields.  I want to return
all rows for the most recent date in the database.  How can I write a sql
statement to do this?

Thanks, Michael

 
 
 

Select *, for the most recent date

Post by Zach Well » Fri, 11 Jan 2002 05:00:36


Please provide DDL (Create table statements) and sample data (via INSERT
statements) as your question is unanswerable without more information. All I
can tell you, based on the information provided, is to use MAX().

--
Zach
---
When in doubt, check out the Books Online,
  it's a GREAT source of information!


Quote:> I have a table, with a date field, and many other fields.  I want to
return
> all rows for the most recent date in the database.  How can I write a sql
> statement to do this?

> Thanks, Michael


 
 
 

Select *, for the most recent date

Post by Aaron Bertrand [MVP » Fri, 11 Jan 2002 04:56:20


SELECT TOP 1 * FROM table ORDER BY dateColumn DESC

However, you should consider avoiding SELECT * -- name your fields.

www.aspfaq.com


Quote:> I have a table, with a date field, and many other fields.  I want to
return
> all rows for the most recent date in the database.  How can I write a sql
> statement to do this?

> Thanks, Michael

 
 
 

Select *, for the most recent date

Post by Joe Celk » Fri, 11 Jan 2002 05:01:49


SELECT *
  FROM Foobar
 WHERE mydate
       = (SELECT MAX(mydate) FROM Foobar);

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Select *, for the most recent date

Post by Chakkara Rangaraja » Fri, 11 Jan 2002 05:07:19


select * from table_1
where table_1_date_1 in (select max(table_1_date_1)
                         from table_1)
if you want, cut down the time part. You can also '</>/='
in place of 'in' operator
Quote:>-----Original Message-----
>I have a table, with a date field, and many other

fields.  I want to return
Quote:>all rows for the most recent date in the database.  How
can I write a sql
>statement to do this?

>Thanks, Michael

>.

 
 
 

Select *, for the most recent date

Post by Leonid Kozhinsk » Fri, 11 Jan 2002 17:55:06


SELECT TOP 1 WITH TIES *
FROM your_table_name
ORDER BY your_date_field DESC

or

SELECT * FROM your_table_name
WHERE your_date_field  = (SELECT MAX(your_date_field)   FROM
your_table_name)

Leonid Kozhinsky



Quote:> I have a table, with a date field, and many other fields.  I want to
return
> all rows for the most recent date in the database.  How can I write a sql
> statement to do this?

> Thanks, Michael

 
 
 

1. Select Count and Most Recent Date

I'm trying to create a view of recrods with the most recent date and a count
of those with a common value.

RecordID    AuditID        AuditDate    Auditor
1                    AUD14      01/01/2003    JSmith
2                    AUD11      04/01/2003    LCantri
3                    AUD10      01/11/2003    TBlack
4                    AUD14      11/07/2003    DMorri

To display like this:

Count    AuditID        LastAuditDate    LastAuditor
2           AUD14      11/07/2003            DMorri
1           AUD11      04/1/2003              LCantri
1           AUD10      01/11/2003            TBlack

Is this possible?

2. Does Full-Text Search work in a clustered environment?

3. Question: How can I select only recent dates?

4. US-CT-Hartford Programmer-Analyst, UNIX, C/C++, Oracle Forms, COBOL (1312)

5. Select records by Dates in the recent month

6. need help to make an odbc connection

7. SQL: Select some data with date criteria, but only by Date part of date field

8. D3 Pro Plus intall, any warnings?

9. query most recent date

10. Most recent date?

11. Foxpro: Reporting/Querying on most recent date in DBF

12. finding the most recent date and y2k

13. Recent Date