SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get last row from Query!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

padsp
Starting Member

USA
21 Posts

Posted - 02/21/2006 :  20:08:24  Show Profile  Reply with Quote
Hi,

I have table called eDates with following structure:
Table: eDates
eID eExpDt eActDt eMtgID
100 2004-08-19 2004-08-26 458
100 2005-04-19 2005-05-06 478
100 2005-12-09 1900-01-01 0
101 1995-08-14 1995-08-25 100

I would like to get last row based on eID; like for 100 i would like to get the following output:
100 2005-12-09 1900-01-01 0

I have used this query to get it:
SELECT Top 1 * FROM eDates WHERE eID=100 ORDER BY 'eDates' DESC

What happen's is i get the row for the first time; then i validate and INSERT another record for the same eID. Again if i execute this query i'm getting previous row instead of last row(Inserted one).

Your help is greatly appreciated.

Thanks
PP

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 02/21/2006 :  20:30:05  Show Profile  Reply with Quote
What is the eDates for the new record that you inserted. Is it greater than '2005-12-09' ?

----------------------------------
'KH'

It is inevitable
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/21/2006 :  21:42:52  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>>ORDER BY 'eDates' DESC

The single quotes aremost likely causing a sort on the literal 'eDates', and not the column value. Simply remove the quotes.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17638 Posts

Posted - 02/21/2006 :  21:48:48  Show Profile  Reply with Quote
quote:
Originally posted by jsmith8858

>>ORDER BY 'eDates' DESC

The single quotes aremost likely causing a sort on the literal 'eDates', and not the column value. Simply remove the quotes.


Oh did not noticed that at all

----------------------------------
'KH'

It is inevitable
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22760 Posts

Posted - 02/22/2006 :  01:01:04  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Using single quotes on the column will give the result in different order. In SQL Server you will get a single row and if you try this in Access you can see all rows with Dates in Descending Order

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

padsp
Starting Member

USA
21 Posts

Posted - 02/22/2006 :  10:26:51  Show Profile  Reply with Quote
Hi all,
Thanks for your reply. Here are more details.
1. The reason why i gave sigle quote is,eDates is a table name. This is not a column. If i give without quote, throws me error like Invalid column name. The idea is to order DESC entire table, so i can take the first row.
2.The query gives me for the first time. Then i validate and Insert another record like this,
INSERT INTO eDates(eID,eExpDt)
VALUES(100,'2006-04-09')

3.Ofter executing above insert stmt, I would like to get the last row, which is i just inserted.

Someone could tell me what am i missing here.
Thanks
PP
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 02/22/2006 :  10:35:41  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
quote:

1. The reason why i gave sigle quote is,eDates is a table name. This is not a column. If i give without quote, throws me error like Invalid column name. The idea is to order DESC entire table, so i can take the first row.



You are making up your own syntax for SQL that doesn't exist ... Why do you think that you can "order by an entire table"? "Fixing" an error by putting quotes on the expression means that you are sorting on a varcahr() expression ('eDates'). This is the equivalent of not sorting at all, since it is constant for all rows in your table.

SQL Server is a relational database, meaning that there is no concept of first row, last row, or row number. Data is simply data. If you want to sort rows in a certain way, you must do so according to the data that you have stored in the rows/columns in the table. So, if you want to return rows in the order that they were entered, you must record the datetime that each row was added in a datetime column. Then, you can order by that column DESC or ASC to get what you are looking for.
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts

Posted - 02/22/2006 :  10:36:20  Show Profile  Reply with Quote
You have to pick a field to sort your result set by sounds like you want
ORDER BY eID DESC

If you're just looking for the last row:
SELECT fields
FROM eDates
WHERE eID = (SELECT MAX(eID) FROM eDates)
Go to Top of Page

padsp
Starting Member

USA
21 Posts

Posted - 02/22/2006 :  11:27:13  Show Profile  Reply with Quote
Thank you all for your input. I'll order the table based on one of the date column.

Now i have another question please....
As you can see date column has null values like '1900-01-01 00:00:00' or NULL
In one of my procedure i would like to validate if it's null value, then replace with something.
I have tried ISNULL(datecol), actually the value is '1900-01-01' or NULL.
How do i come across in this case. Do i need to hardcode like,
if (dtcol='1900-01-01' OR NULL)

thanks
PP
Go to Top of Page

JoeNak
Constraint Violating Yak Guru

USA
292 Posts

Posted - 02/22/2006 :  12:38:12  Show Profile  Reply with Quote
You're using ISNULL incorrectly. Should be ISNULL(datecol, replacement value)

Yes, could do something like...
IF datecol = '1900-01-01' or datecol IS NULL
Go to Top of Page

padsp
Starting Member

USA
21 Posts

Posted - 02/22/2006 :  12:48:22  Show Profile  Reply with Quote
Hi,
I'm using the condition like this,

IF(datecol = '1900-01-01 00:00:00' OR datecol IS NULL)

I wonder is there any other better way to do this.

Thanks
PP
Go to Top of Page

pepegrillo113
Starting Member

Costa Rica
2 Posts

Posted - 03/30/2012 :  15:09:23  Show Profile  Reply with Quote

Thank you man.... I has helped me a lot






quote:
Originally posted by JoeNak

You have to pick a field to sort your result set by sounds like you want
ORDER BY eID DESC

If you're just looking for the last row:
SELECT fields
FROM eDates
WHERE eID = (SELECT MAX(eID) FROM eDates)



software
Go to Top of Page

pepegrillo113
Starting Member

Costa Rica
2 Posts

Posted - 03/30/2012 :  15:13:23  Show Profile  Reply with Quote

Thank You man, it has helped me a lot..

quote:
Originally posted by JoeNak

You have to pick a field to sort your result set by sounds like you want
ORDER BY eID DESC

If you're just looking for the last row:
SELECT fields
FROM eDates
WHERE eID = (SELECT MAX(eID) FROM eDates)



software
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000