|
Hi Friends,
I have a table with fileds ID,code,descripiton, date
where ID is primaryKey also date is a unique while code and description could hame same data.
my problem is i have to fetch last two records for each of the same code and description in the table.
How can i do that please?
Please not that
"select top 2 * from table1 order by id des"c is returning only last 2 recods of the table.
While i need last 2 records for each of the code and description in the table.
thanks
Naveed Kamboh
|
|
|
|
|
Hi,
okay I will try my best, so I hope this will help you. My idea is to use a Stored Procedure or something like this, where you build a loop over all value-pairs of code and description (can be retrieved by a distinct or a group-by). And inside that loop you select the last two records using a where-condition to identify the matching records. Afterwards you can put these selected rows into a temporary table. Perform a select at the end to retrieve the result.
Pseudo-code:
<br />
create temporary table X (must have same scheme like your original table)<br />
<br />
foreach value-pair E of code and description<br />
begin<br />
insert into X <br />
select top 2 * from original_table where code = E.code and description = E.description<br />
end<br />
<br />
select * from X<br />
Hmmm, i hope this helps
Regards Sebastian
-- modified at 9:02 Tuesday 6th June, 2006
|
|
|
|
|
Assuming MS SQL, you could try something like this:
SELECT
t.*
FROM
table1 t
WHERE
t.date = (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description)
OR
t.date = (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description AND
date < (SELECT MAX(date)
FROM table1
WHERE code = t.code and
description = t.description))
|
|
|
|
|
That's by far your best option. There's a way to do it with grouping also which may come out faster depending on the DBMS and index structure. Either way, this is a much better solution than using a cursor.
|
|
|
|
|
We had a problem like this, some time ago... and the solution were to use stored procedured with cursors....
( The performance.... we know )
What we did is to build the cursor at runtime, with the Execute () Method... setting here the Order By... after that create the Cursor and Fetch till the Value needed... aslo notice you can specify the cursor to fetch directly a row
Alter Procedure DoLoop
as
Declare curMyLoop Cursor For
Select * from spt_values
Open curMyLoop
Fetch Next from curMyLoop
while @@Fetch_status = 0
Begin
Fetch Next from curMyLoop
End
deallocate curMyLoop
Go
alter procedure pp
as
declare @str varchar(50)
set @str= 'Select * from spt_values'
Execute ( @str )
Go
Regards
Ricardo Casquete
|
|
|
|
|
Thanks to all,
It realy helped me.
Naveed Kamboh
|
|
|
|
|
Hi all,
I am working for information warehouse application. Here database used is Oracle.Here we are loading data from flat file to oracle tables using SQL Loader. Which is getting failed and showing error
ORA 00001 unique constraint violated;
I have checked for the corresponding table by using DESC <table_name>;
It is showing NOT NULL constraint for all the columns. There is no primary key for the table.
Please guide me, what can be the expected reasons for this failure.
|
|
|
|
|
Hi,
hmmm sounds difficult. Maybe Oracle uses all columns as primary key (when no primary key is declared).
Did you tried to insert some rows manually? Did you get the same error? Try to insert two rows with all the same column values.
Hope this helps.
Regards
Sebastian
|
|
|
|
|
Hi,
I don't have right to insert the values manually.
Thanks for ur response.
|
|
|
|
|
It is possible to have constraints other than primary key or null constraints. Have you checked indexes on the table, or other constraints. I'm rusty on my Oracle, but every other database I've used lately has an option to temporarily disable constraint checking for data loads.
|
|
|
|
|
I have checked for the primary key by using DESC <table_name>. Is it the right way to check.
I am new to database can u please tell me how to check for indexes and primary key.
Thanks for ur response.
|
|
|
|
|
Can anyone tell me how to write a sql query to get a comma separated list of values.
Eg.
IF the table ColorTable contains in it's colors field red,blue and green.
The query "Select colors from ColorTable" would return 3 rows with each row containing each color.
I would like to have a query that would return the colors in one row as red,blue,green instead of returning 3 rows
I tried using list(colors) but this function is not recognised.
Kindly help me,
Thanks in advance
|
|
|
|
|
select (<br />
(select description from colortable where description='green') + ',' + <br />
(select description from colortable where description='Blue') + ','+ <br />
(select description from colortable where description='Red')<br />
) as colors from colortable
|
|
|
|
|
Is there any other way without hard coding the values and individually concatenating them as I have to retrieve data from a table with numerous records based on some conditions.
|
|
|
|
|
You can do something like this:
<br />
<br />
declare @list varchar(1000)<br />
<br />
select @list = isnull(@list, '') + colors + ',' from ColorTable<br />
<br />
select @list<br />
<br />
It's got some extra "features" like that it always ends in a comma, but for every issue you may have with it there's a reasonable solution. Ask if you need more help with that.
|
|
|
|
|
That was just what i needed.
Thanks a lot.
By using the following i have removed the comma that appears at the end of the line
select substring(@list,1,len(@list)-1)
Now can you please tell me if there is a way in which the comma before the last item can be replaced with 'or'
i.e. I would like the output to be like
red,blue,green,..........pink or yellow
-- modified at 0:55 Thursday 8th June, 2006
|
|
|
|
|
Sure, try this:
<br />
declare @list varchar(1000)<br />
declare @len int<br />
<br />
select @list = isnull(@list, '') + colors + ',', @len = len(colors) from ColorTable<br />
<br />
select @list = substring(@list, 1, len(@list)-1)<br />
select substring(@list, 1, len(@list) - @len - 1) + " or " + substring(@list, len(@list) - @len, @len)<br />
I'm not in front of a SQL Server right now so I can't try it easily, and I may be off by a character (and thus a -1 or +1 in the substring lengths) here and there. I'm sure you can figure out the tweaks, though.
The concept is to have a variable hold the length of the last element. It does this by constantly replacing its value with the length of the current element; at the end of the query, it will contain the length of the last element. Once you know that, it's a matter of string handling to cut your list up and replace text as necessary.
Two more things:
1. If there's a possibility that ColorTable may contain zero, one or two rows, or if you're using a where clause that may return only zero, one or two rows, you may want either some if/then or case when clauses with select count statements to ensure you don't return errors.
2. While it's possible to do this in SQL, as demonstrated, there are some things that are better left to code. I assume you have a really good reason for doing it on the SQL Server instead of in whatever language you're using for your queries, but in general I'd recommend this be done in a code library instead of on the DB server.
|
|
|
|
|
Thanks a lot for your help.
Thanks a lot for taking the time to reply.
|
|
|
|
|
Thanks.It's just what I needed.
|
|
|
|
|
Hi all..
Can any one tell me how i can find second largest value of perticular
attribute. Please give query fetching second largest value.
Rahul Kulkarni
|
|
|
|
|
Try:
<br />
select Max(attr)<br />
from table<br />
Where attr < (select Max(attr) from table)<br />
Wout Louwers
|
|
|
|
|
what is about 7th highest. can you tell me plz
The secret of life is not enjoyment
but education through experience.
- Swami Vivekananda.
|
|
|
|
|
Select *,sal From Emp X Where n =<br />
(<br />
Select Count(Distinct Sal) From Emp Where sal >=X.sal<br />
)
Where n = 7
|
|
|
|
|
Thanks It is the correct answer . Thanks Again.
The secret of life is not enjoyment
but education through experience.
- Swami Vivekananda.
|
|
|
|
|
Hello Friends,
May be I am missing some thing here while trying to build a dynamic WHERE clause.
this is the final version of my Stored pror
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[USP_SearchVehicleOnYMM]
@Year char(4)
,@Make int
,@Model int
AS
BEGIN
SET NOCOUNT ON;
IF LEN(@Year)<=0
begin
select @Year = NULL
end
IF LEN(@Make)<=0
begin
select @Make = NULL
end
IF LEN(@Model)<=0
begin
select @Model = NULL
end
SELECT V.vin
,C.FirstName
,C.LastName
,C.AccountNum
,D.DealerShipName
,Contrt.MaturityDate
,VS.GroundedDate
,V.[Year]
,VMkL.VehicleMakeDesc
,VMdL.ModelDesc
FROM Vehicle V
Inner Join ContractInfo contrt ON Contrt.VehicleID = V.VehicleID
Left Join DealerShips D ON v.ManufacturerID = D.ManufacturerID
Inner Join Customer C ON Contrt.CustomerID = C.CustomerID
Inner Join VehicleStatus VS ON Contrt.VehicleID = VS.VehicleID
Left Join ManufacturerMakeModel mmm ON v.MakeID = mmm.makeID
left Join VehicleMakeLookUp vmkl ON mmm.MakeID =
vmkl.VehicleMakeID
left Join VehicleModelLookup VMdl ON mmm.ModelID =
VMdl.ModelID
WHERE
((@Year IS NULL ) OR (V.Year=@Year))
and
(( @make = 0 ) OR (VMkL.VehicleMakeDesc=@make))
and
(( @Model = 0 ) OR (VMdL.ModelDesc=@Model))
END
And when I exectue (right click on the stored proc in management studio
and pick the exectue option) this with out providing any values to the
Year, Make and Model I am getting this error
**********************************Error*****************************************************
Msg 201, Level 16, State 4, Procedure USP_SearchVehicleOnYMM, Line 0
Procedure or Function 'USP_SearchVehicleOnYMM' expects parameter
'@Year', which was not supplied.
(1 row(s) affected)
***************************************************************************************************
Could some one please fix this error and have me understand where I am doing wrong?
Thanks
-L
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
