How to get last row from Query!

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

How to get last row from Query!

Author

Topic

padsp
Starting Member

21 Posts

Posted - 2006-02-21 : 20:08:24

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)

17689 Posts

Posted - 2006-02-21 : 20:30:05

What is the eDates for the new record that you inserted. Is it greater than '2005-12-09' ?

----------------------------------
'KH'
It is inevitable

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-21 : 21:42:52

>>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.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-21 : 21:48:48

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-22 : 01:01:04

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

padsp
Starting Member

21 Posts

Posted - 2006-02-22 : 10:26:51

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-22 : 10:35:41

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.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-22 : 10:36:20

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)

padsp
Starting Member

21 Posts

Posted - 2006-02-22 : 11:27:13

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

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-02-22 : 12:38:12

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

padsp
Starting Member

21 Posts

Posted - 2006-02-22 : 12:48:22

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

pepegrillo113
Starting Member

2 Posts

Posted - 2012-03-30 : 15:09:23

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

pepegrillo113
Starting Member

2 Posts

Posted - 2012-03-30 : 15:13:23

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources