Want to Select the row with highest date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Want to Select the row with highest date

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-10-24 : 17:30:30

Hello,

I have this select statement:
SELECT DISTINCT FILE_NAME,CONVERT(VarChar,DATE,107) AS DATE...bla bla bla.
It gives me same files more than once because some other column (like a status column) has different values in them. I want to select only the one with highest date value. I tried MAX(DATE) (I must be stupid) but that doesn't work.

Any idea? Thanks.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 17:37:44

SELECT ...
where DATE = (select MAX(DATE) from ...)

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-10-24 : 17:53:38

Thanks so much hanbingl. One more thing, even though Im using order by Date - its not working, any idea?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 18:18:34

you'll have to show me your query...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-25 : 01:59:37

if you're using MAX(Date) as hanbingl suggested you dont need ORDER BY.We cant suggest anything further unless we see query used and also sample data you have with expected output.

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-10-27 : 11:31:51

Sorry for the delay, couldn't check during weekend. Here's the query:

SELECT DISTINCT FILE_NAME,CONVERT(VarChar,DATE,107) AS DATE FROM S_INFO SI,SC,AC WHERE DATE = (SELECT MAX(DATE) FROM S_INFO WHERE
FILE_NAME = SI.FILE_NAME) AND .......
ORDER DATE, FILE_NAME DESC

Sample Output:
--------------
WC_1100_Oct07.DAT Apr 20, 2007
WC_1100_Oct07.DAT Apr 22, 2007
FA_1400_OCT07.DAT Dec 15, 2007
FA_1400_OCT07.DAT Dec 21, 2007
AE_0600_Oct07.DAT Feb 11, 2008
AE_1800_Sep07.DAT Jan 13, 2008
WB_1200_OCT07.DAT Jul 01, 2007
WB_1200_OCT07.DAT Jul 11, 2007

As you can see, it doesn't order by.
Thanks.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 11:35:45

What is the datatype of your DATE column?? Right now it is Ordered as char string not as DATE.

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-10-27 : 15:57:22

The datatype is datetime.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 16:08:29

try force it to:


ORDER by cast(DATE as smalldatetime), FILE_NAME DESC

sqlbug
Posting Yak Master

201 Posts

Posted - 2008-10-27 : 16:23:40

I got it. Because I used convert on DATE, and the format was Mon dd, yyyy, it was ordering by the month name - that you pointed out. So instead im using yyyy.mm.dd (102, ANSI) and its working fine.
Thanks so much.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-27 : 17:15:18

well.. don't do Order by convert(varchar...)
Just order by the original DATE column and you should be fine.. You can display it in any format.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 00:12:38

you should always try to use proper datatypes for your variable. By using varchar field for storing datevalues, you're making date manipulations complex. Also other problems have to be dealt with like sorting.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 04:04:14

Always let the front end to do date formations
Also see why you need to use size for varchar during conversion
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources