|
View previous topic :: View next topic
|
| Author |
Message |
jeroznishanth
New User
.jpg)
Joined: 21 Nov 2007
Posts: 8
Location: Bangalore
|
|
|
|
I have to search a set of rows with a particular rule.
Eg: rows whose name = xyz.
Then I will get some 5 rows.
Id name
2 xyz
3 xyz
4 xyz
5 xyz
now I want to fetch only the last record.
that is
Id name
5 xyz
Id is a varchar
Please provide me the one single query to execute this.
thnaks in advance |
|
| Back to top |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
did You sarch the forums, looks like not,
if You had you would have found many topics dicussing the issue |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| Code: |
select Id name from tablename order by Id desc fetch first 1 rows only
|
|
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
Hi Sambhaji,
i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected). |
|
| Back to top |
|
|
Escapa
Senior Member
Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA
|
|
|
|
| Quote: |
i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected).
|
Thats what we wanted here if I have understood the requirement..
Can you explain with example what you wanted to say? |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
The input provided is in the sorted order. But in real time the SELECT statement may or maynot be in the sorted order. So if the SELECT stmt is not in the sorted order the last record won't be the same as when you provide
| Quote: |
order by Id desc fetch first 1 rows only
|
|
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| arvind.m wrote: |
Hi Sambhaji,
i have a question. If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected). |
If you do not use order by, you don't know what to expect. DB2 does not retrive the rows in any particular order unless you specify an ORDER BY. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| arvind.m wrote: |
| If you use ORDER BY caluse will that won't change the order and gives a differet output (than what expected). |
Without an ONDER BY You just arbitrarily selected a row. It could be different the next time you do it, or it could be the same for the life of the table. Again, this is a complete waste of a query without an ORDER BY clause. |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
Use this query, if you want:
| Code: |
select * from table1
where id = (select max(id) from table1)
fetch first row only |
Lenny |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
the query would be right if the topic starter had specified a reasonable rule...
asking for the last one without any concern for the order,max, min or any other sql compliant constraint is just poor understanding of how sql works |
|
| Back to top |
|
|
arvind.m
Active User
Joined: 28 Aug 2008
Posts: 205
Location: Hyderabad
|
|
|
|
| Yes. i agree with enrico. |
|
| Back to top |
|
|
|
|
